Reputation: 11
I have a table with the structure below:
Product | Status | Quantity
____________________________________________
P001 | 1 | 50
P001 | 1 | 30
P001 | 2 | 40
P001 | 2 | 60
I would like to sum the quantity which group by Product and Status. The result will be:
Product | Status 1 QTY | Status 2 QTY
____________________________________________
P001 | 80 | 100
Can the above be done in ONE sql query?
Thanks!
Upvotes: 1
Views: 115
Reputation: 263943
It can be done through CASE
statement.
SELECT Product,
SUM(CASE WHEN Status = 1 THEN Quantity ELSE 0 END) `Status 1 QTY`,
SUM(CASE WHEN Status = 2 THEN Quantity ELSE 0 END) `Status 2 QTY`
FROM TableName
GROUP BY Product
If you have unknown number of values for Status
, better do it dynamically.
Upvotes: 2