SQL GROUP BY and COUNT and conditional SUM with column value

Question

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.

It is called conditional aggregation.

Sergey
– Sergey

2022-04-29 10:37:20 +00:00
Commented Apr 29, 2022 at 10:37 — Sergey
– Sergey, Commented Apr 29, 2022 at 10:37

Piyush Kachhadiya · Accepted Answer · 2022-04-29 10:39:08Z

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

answered Apr 29, 2022 at 10:39

Piyush Kachhadiya

6321 gold badge8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ManthaN N Patel Over a year ago

you need to improve your query, what if more than two status code exist. you may use PIVOT

Piyush Kachhadiya Over a year ago

It's Dynamic if more then two states then add more case when you need just change column name. @MANthanNPatel if you agree then vat it.

forpas Over a year ago

@MANthanNPatel the question is tagged MySql and there is no PIVOT in MySql. Also Status is obviously a Boolean column.

ManthaN N Patel Over a year ago

Not mention bool, also tagged sql, anyways have a good day : )

forpas · Accepted Answer · 2022-04-29 11:18:05Z

1

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.

answered Apr 29, 2022 at 11:18

forpas

165k10 gold badges51 silver badges85 bronze badges

4 Comments

Abhijit Over a year ago

this also works, but if status define in string then not work e.g. status = 'active' or something :-)

forpas Over a year ago

@Baisnabi This is an answer for the question that you posted and not any other question where status is string. Did you read my answer from the start: "Assuming that the data type of Status..."

Abhijit Over a year ago

My bad I upvoted you and I thing @PiyushKachhadiya answer first, so ...

forpas Over a year ago

@Baisnabi my comment has nothing to do with your voting. I responded to your comment which mentions that my query would not work if....

Collectives™ on Stack Overflow

SQL GROUP BY and COUNT and conditional SUM with column value

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related