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: dba.stackexchange.com/a/23118/152804.Chofoteddy– Chofoteddy2018-06-05 15:50:05 +00:00Commented Jun 5, 2018 at 15:50
Add a comment
|
7 Answers
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
7 Comments
eggyal
Or just
SUM(color='YELLOW') etc.
eisberg
@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)).
TrtG
Thanks for this one. And the weight thing can be useful also.
man
Actually, if you want to return an array with counts for each color, this solution is a better fit.
Yang Hu
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.)
|
SELECT color, COUNT(*) FROM t_table GROUP BY color
3 Comments
AdrianBR
if the table has more colors, query above returns all colors, not just the 3 selected
eggyal
@AdrianBR: That's taking the question rather literally, and failing to see through to the actual underlying problem.
josephdpurcell
@eggyal
SELECT color, COUNT(*) FROM t_table WHERE color IN ('YELLOW', 'BLUE', 'RED') GROUP BY colorSELECT '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
1 Comment
AdrianBR
you accepted the only incorrect answer as to your request. your accepted answer returns all colors, not just the 3 you selected.
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'
);
4 Comments
Paul Gordon
It appears you need parenthesis around the nested SELECT statements, at least in PostgreSQL.
Ali Sherafat
are you sure this is one query?!!
Faisal
yeah @AliSherafat
Anirudha Gupta
@Faisal If I need to write similar query to update the 1 table from another table with when then, how it can be done.
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
Comments
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;
Comments
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.
