CODEWITHSUNDEEP
mysqlsqlcount
TrtG
TrtG

Reputation: 2848

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

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?

Upvotes: 41

Views: 123487

Answers (7)

AHARON BEN EFRAYIM
AHARON BEN EFRAYIM

Reputation: 1

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.

Upvotes: 0

Billy Mainor Lemus
Billy Mainor Lemus

Reputation: 11

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;

Upvotes: 0

Sinte
Sinte

Reputation: 91

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

Upvotes: 1

Faisal
Faisal

Reputation: 4765

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'
);

Upvotes: 3

eisberg
eisberg

Reputation: 3771

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

Upvotes: 76

AdrianBR
AdrianBR

Reputation: 2588

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

Upvotes: 9

eggyal
eggyal

Reputation: 125855

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

Upvotes: 63

Related Questions