CODEWITHSUNDEEP
mysqlaggregate-functions
Kim Stacks
Kim Stacks

Reputation: 10822

How do I write in a single query two different sum queries with different conditions?

I have a MySQL datatable items as below:

+----+-------+----------+----------+
| id | value | discount |   type   |
+----+-------+----------+----------+
|  1 |    20 |        1 | hardware |
|  2 |    40 |        0 | hardware |
|  3 |    60 |        1 | software |
|  4 |    30 |        1 | software |
+----+-------+----------+----------+

When discount is 1, this means that effectively the value is ZERO.

I want to get back the following results

+----------+----+
| software |  0 |
| hardware | 40 |
+----------+----+

I know how to do this in multiple queries.

SELECT type, SUM(value) from items where discount != 1 group by type

which gives me just

+----------+----+
| hardware | 40 |
+----------+----+

and then 

SELECT type, 0 from items where discount = 1 group by type

which gives me

+----------+----+
| software |  0 |
| hardware |  0 |
+----------+----+

then i need to join these two tables to get the final result.

My question is :

is there a way I can accomplish the same result with just 1 query?

Thank you.

Upvotes: 0

Views: 44

Answers (1)

Keshav jha
Keshav jha

Reputation: 1364

I think this is what you want 

SELECT type, SUM(if(discount =1, 0,value)) from items group by type

Upvotes: 2

Related Questions