Multiple COUNT() for multiple conditions in one query (MySQL)

Question

I have these queries :

SELECT COUNT(*) FROM t_table WHERE color = 'YELLOW';
SELECT COUNT(*) FROM t_table WHERE color = 'BLUE';
SELECT COUNT(*) FROM t_table WHERE color = 'RED';

Is there any way to get these results in one query?

Additional answer: https://dba.stackexchange.com/a/23118/152804. — Chofoteddy, Jun 05 '18 at 15:50

score 71 · Answer 1 · answered Sep 27 '13 at 09:35

71

If you want the result to be in one row you can use:

SELECT
    SUM(IF(color = 'YELLOW', 1, 0)) AS YELLOW,
    SUM(IF(color = 'BLUE', 1, 0)) AS BLUE,
    SUM(IF(color = 'RED', 1, 0)) AS RED
FROM t_table

Working example

answered Sep 27 '13 at 09:35

eisberg

3,731
2
27
38

9

Or just `SUM(color='YELLOW')` etc. – eggyal Sep 27 '13 at 09:37
1

@eggyal Yes, but it is hard to see what is going on here and you cannot choose to count with weights like `SUM(IF(color = 'YELLOW', 0.75, 0))`. – eisberg Sep 27 '13 at 09:39
Thanks for this one. And the weight thing can be useful also. – TrtG Sep 27 '13 at 10:00
Actually, if you want to return an array with counts for each color, this solution is a better fit. – man Oct 14 '14 at 10:53
Great answer. This is better than the accepted one because sometimes you do not have something that can be grouped. (like when query with different time ranges.) – Yang Hu Oct 26 '15 at 17:57
Nice bro, very simple but easy to custom 'GROUP BY' – Ecko Santoso Jan 11 '16 at 02:49
Good for rows less than few millions, but not a good choice – Pawan Saxena Mar 03 '21 at 14:23

score 62 · Accepted Answer · answered Sep 27 '13 at 09:06

62

SELECT color, COUNT(*) FROM t_table GROUP BY color

answered Sep 27 '13 at 09:06

eggyal

122,705
18
212
237

3

if the table has more colors, query above returns all colors, not just the 3 selected – AdrianBR Sep 27 '13 at 10:06
18

@AdrianBR: That's taking the question rather literally, and failing to see through to the actual underlying problem. – eggyal Sep 27 '13 at 10:16
8

@eggyal `SELECT color, COUNT(*) FROM t_table WHERE color IN ('YELLOW', 'BLUE', 'RED') GROUP BY color` – josephdpurcell Dec 10 '13 at 19:21

score 9 · Answer 3 · answered Sep 27 '13 at 09:06

9

SELECT 'yellow' as color ,COUNT(*) FROM t_table WHERE color = 'YELLOW'
union
SELECT 'blue' , COUNT(*) FROM t_table WHERE color = 'BLUE'
union
SELECT 'red',COUNT(*) FROM t_table WHERE color = 'RED';

or

select color, count(*) from table where color in ('red', 'blue', 'yellow') group by 1

answered Sep 27 '13 at 09:06

AdrianBR

2,762
1
15
29

2

you accepted the only incorrect answer as to your request. your accepted answer returns all colors, not just the 3 you selected. – AdrianBR Sep 27 '13 at 10:07

score 3 · Answer 4 · answered Feb 01 '17 at 07:31

3

You can do this using subquery.

SELECT(
    SELECT COUNT(*) FROM t_table WHERE color = 'YELLOW',
    SELECT COUNT(*) FROM t_table WHERE color = 'BLUE',
    SELECT COUNT(*) FROM t_table WHERE color = 'RED'
);

answered Feb 01 '17 at 07:31

Faisal

4,591
3
40
49

1

It appears you need parenthesis around the nested SELECT statements, at least in PostgreSQL. – Paul Gordon May 19 '17 at 16:09
2

are you sure this is one query?!! – Ali Sherafat Sep 06 '17 at 07:49
yeah @AliSherafat – Faisal Sep 06 '17 at 17:31
@Faisal If I need to write similar query to update the 1 table from another table with when then, how it can be done. – Anirudha Gupta Jun 02 '18 at 05:47

score 1 · Answer 5 · edited Apr 13 '18 at 07:35

I think this can also works for you

select count(*) as anc,(select count(*) from Patient where sex='F')as 
        patientF,(select count(*) from Patient where sex='M') as patientM from anc

you can also even select and count related tables like this

select count(*) as anc,(select count(*) from Patient where 
    Patient.Id=anc.PatientId)as patientF,(select count(*) from Patient where
    sex='M') as patientM from anc

score 0 · Answer 6 · edited Sep 28 '22 at 12:47

This is my answer:

SELECT sm_med_t_servicios.id as identidad, count(sm_adm_t_admision.id) as cantidad , 
SUM(IF(sm_adm_t_admision.atendido = 'S', 1, 0)) AS atendidos,
SUM(IF(sm_adm_t_admision.atendido = 'N', 1, 0)) AS por_ver

FROM sm_med_t_servicios 
LEFT JOIN sm_adm_t_admision ON sm_med_t_servicios.id = sm_adm_t_admision.sm_med_t_servicios_id
WHERE sm_med_t_servicios.m_empresas_id = '2'
GROUP BY sm_med_t_servicios.id

I hope this helps you.

score 0 · Answer 7 · answered Sep 06 '22 at 16:52

A little bit late but taking Sinte's Answer

[case] queries requirements -mutiple item names with mutiple counts

select 
t1.person_name

,(
    select count(person_id) from persons where sex='F' and person_id=t1.person_id
)as quantity_M


,(
    select count(person_id) from persons where sex_id='M' and person_id=t1.person_id
) as quantity_F


from persons as t1
group by t1.person_name
order by t1.person_name;

Multiple COUNT() for multiple conditions in one query (MySQL)

7 Answers7

Linked