How to use multiple COUNT in SQL

Question

In my table I have task, every task have state.

1 - planned
2 - executing
3 - finished

I want get count of all planned, executing and finished task.

I can write three queries like this:

SELECT COUNT(*) FROM `task` WHERE state = 1
SELECT COUNT(*) FROM `task` WHERE state = 2
SELECT COUNT(*) FROM `task` WHERE state = 3

So, my question is: It is possible (and how?) get this data in one query?

Thank you for any help.

You should tag your question with the database you are using — Gordon Linoff
– Gordon Linoff, Commented Jan 25, 2018 at 22:20

Iłya Bursov · Accepted Answer · 2018-01-25 22:21:13Z

6

another approach is to use group by, like this:

select state, count(*)
from task
group by state

answered Jan 25, 2018 at 22:21

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

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

1 Comment

RoMEoMusTDiE Over a year ago

of course.. funny how we can easily forget other methods aye!

RoMEoMusTDiE · Accepted Answer · 2018-01-25 22:18:33Z

5

you use SUM

select state,
sum(case when state = 1 then 1 else 0 end) state1count,
sum(case when state = 2 then 1 else 0 end) state2count,
sum(case when state = 3 then 1 else 0 end) state3count
from task

answered Jan 25, 2018 at 22:18

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to use multiple COUNT in SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related