CODEWITHSUNDEEP
mysqlsql-server
Adonis.C
Adonis.C

Reputation: 3

SQL display the total sales

I want to select a total sales of each Category Here is my table: (catNo = Category No. prodNo = product No.)

OrderLine:
prodNo ordNo actualPrice qty
P0001  OR001 3.00        20
P0002  OR002 3.00        2
P0003  OR003 500.00      25
Product:    
prodNo prodName  prodPrice   prodPhoto       stockQty catNo  suppNo
P0001  OverPower 1500.00     OP_C4_Black.jpg 10       CAT05  S0001
P0002  Vain      300.00      Vain.jpg        5        CAT04  S0002
P0003  test      500.00      test.jpg        40        CAT05 S0001

my SQL command is 

SELECT `catNo` , sum(`actualPrice`*`qty`)as `Total Sales` 
FROM `orderline` , `product` 
WHERE `orderline`.`prodNo` = `product`.`prodNo` 
GROUP BY `orderline`.`prodNo`;

What I want is 

catNo   Total Sales
CAT05   12560.00
CAT04   6.00

What actual output is

catNo   Total Sales
CAT05   60.00
CAT04   6.00
CAT05   12500.00

How can I display the total sales of each Category?

Upvotes: 0

Views: 5099

Answers (3)

Raging Bull
Raging Bull

Reputation: 18767

Group by catNo. Then you will get the desired result:

SELECT `catNo` , sum(`actualPrice`*`qty`)as `Total Sales` 
FROM `orderline` , `product` 
WHERE `orderline`.`prodNo` = `product`.`prodNo` 
GROUP BY `catNo`

Result:

catNo   Total Sales
-------------------
CAT04   6
CAT05   12560

Sample result in SQL Fiddle

Upvotes: 1

krivtom
krivtom

Reputation: 24916

Instead of grouping by product number you should be grouping by category number:

SELECT `catNo` , sum(`actualPrice`*`qty`)as `Total Sales` 
FROM `orderline` , `product` 
WHERE `orderline`.`prodNo` = `product`.`prodNo` 
GROUP BY `Product`.`catNo`;

Upvotes: 1

Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

SELECT `catNo` , sum(`actualPrice`*`qty`)as `Total Sales` 
FROM `orderline` , `product` 
WHERE `orderline`.`prodNo` = `product`.`prodNo` 
GROUP BY `catNo`;

Try this, I hope it helps.

Upvotes: 1

Related Questions