Select count with union

Question

Select count with union

Navigation

#1 by (2 votes)

1

Hello. I need to do a mysql query with 3 count on the same table. I got my results to appear one below the other, in the same column, but I need them to be in different columns.

It's like this: I need to show the number of students enrolled in ballet classes, the number of students enrolled in jazz classes and the number of students who are enrolled in both classes. From the beginning of the year until today (04/09).

My query:

select count(*) as quantidade_ballet from alunos 
where datamatricula>='2015-01-01%' and datamatricula<='2015-09-04%'
and (indballet=1 and indjazz=0)

union(select count(*) as quantidade_jazz from alunos
where datamatricula>='2015-01-01%' and datamatricula<='2015-09-04%'
and (indjazz=1 and indballet=0))

union(select count(*) as quantidade from alunos
where datamatricula>='2015-01-01%' and datamatricula<='2015-09-04%'
and (indjazz=1 and indballet=1));

PS: indballet and indjazz are the Boolean indicators of which class the student is enrolled in.

Can anyone help me? Thank you.

mysql

asked by anonymous 04.09.2015 / 19:57

1 answer

persistence of data in two or more banks simultaneously [closed] Problems in returning data when creating a search in php

score 2 · Accepted Answer

select 
count(*) as quantidade_ballet,
(select count(*) from alunos
    where datamatricula>='2015-01-01%' and datamatricula<='2015-09-04%'
    and (indjazz=1 and indballet=0)) as quantidade_jazz,

 (select count(*) from alunos
    where datamatricula>='2015-01-01%' and datamatricula<='2015-09-04%'
    and (indjazz=1 and indballet=1)) as quantidade

     from alunos 
    where datamatricula>='2015-01-01%' and datamatricula<='2015-09-04%'
    and (indballet=1 and indjazz=0);

to improve performance use count (id) instead of count (*)