CODEWITHSUNDEEP
mysqlsql
Redone
Redone

Reputation: 1303

Populate Total Sales in Mysql

I am developing an app that needs complex reports like below.

 Total Sales

 Product | Quantity Sold |Total Amount
 --------+---------------+------------
 Coke    |           10  |...
 Pepsi   |            2  |...

And my actual mysql table is as follows. Here the table Name is "sales".

+---+----------+-------+------+--------+
|ID |ProductID |Name   |Price |Quantity|
+---+----------+-------+------+--------+
| 1 |        1 |Coke   |   45 |       2|
| 2 |        1 |Coke   |   45 |       1|
| 3 |        3 |Pepsi  |   50 |       1|
| 4 |        1 |Coke   |   50 |       3|
| 5 |        3 |Pepsi  |   50 |       1|
| 6 |        1 |Coke   |   40 |       1|
| 7 |        1 |Coke   |   45 |       3|
+---+----------+-------+------+--------+

I tried the following query but not getting proper result.

SELECT DISTINCT Name AS Product, Price, SUM(Quantity) AS Quantity Sold FROM sales GROUP BY Price;

Here the price of same product is changing from one row to another. And Total = Price * Quantity.

Any Help would be appreciated. Thanks in advance.

Upvotes: 0

Views: 638

Answers (2)

radar
radar

Reputation: 13425

Select Name as Product ,
Sum(quantity) as 'quantity sold',
Sum(quantity*price) as 'total amount'
Group by name;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You do not have the product in the group by. And, you almost never need select distinct when using group by. So, try this:

SELECT Name AS Product, SUM(Quantity) AS QuantitySold, SUM(Quantity * Price) as TotalAmount
FROM sales
GROUP BY Name;

Upvotes: 1

Related Questions