'SQL GROUP BY and COUNT and conditional SUM with column value

Here is an example of my table.

┌────────┬────────┬───────┐
│ UserId │ Status │ Value │
├────────┼────────┼───────┤
│ 1      │ 1      │ 10    │
│ 1      │ 0      │ 5     │
│ 2      │ 0      │ 8     │
│ 2      │ 1      │ 15    │
│ 1      │ 1      │ 10    │
│ 1      │ 0      │ 5     │
└────────┴────────┴───────┘

I need to GROUP BY rows with UserId then COUNT total rows in the group, to this point I have no problem but I also want to SUM(Value) according Status Column. Like COUNT my sql give me total sum of group rows but I need result like below :-)

┌────────┬──────────────────────┬─────────────────────┐
│ UserId │ SUM(Value) Status=1  │ SUM(Value) Status=0 │
├────────┼──────────────────────┼─────────────────────┤
│ 1      │        20            │        10           │
│ 2      │        15            │        8            │
└────────┴──────────────────────┴─────────────────────┘

NOTE: This type of query called Conditional Aggregation you may search for more about this.

mysqlsql


Solution 1:[1]

use this

SELECT USERID,
SUM(CASE WHEN STATUS = 1 THEN VALUE ELSE 0 END ) AS ST1,
SUM(CASE WHEN STATUS = 0 THEN VALUE ELSE 0 END ) AS ST2 FROM 
DBO.TABLENAME
GROUP BY USERID

Solution 2:[2]

Assuming that the data type of Status is BOOLEAN or INTEGER with only 0 and 1 as possible values:

SELECT UserId,
       SUM(Status * Value) Status_1,
       SUM((NOT Status) * Value) Status_0
FROM tablename
GROUP BY UserId;

See the demo.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Piyush Kachhadiya
Solution 2 forpas

Related Questions