CODEWITHSUNDEEP
phpmysqlsql
Chris Lee
Chris Lee

Reputation: 11

MySQL sum up a column and group by two columns

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

Answers (1)

John Woo
John Woo

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

Related Questions