mercredi 29 juin 2016

How to display data count per category mysql subquery


i have table like bellow

tbl=province

id_province | province
----------------------
01      Province1
02      Province2
03      Province3
...
...

tbl=grade
id_grade | grade
-----------------
A      elementary       
B      junior
C      senior

tbl.transaction
-----------------------------
code    | id_province   | id_grade
--------------------------------
t1          01             A
t2          01             A
t3          01             A
t4          02             A
t5          03             C
t6          02             B
t7          03             A

how i can query if i want to display data like bellow

id_province | province  | count of grade A  | count of grade B | count of grade C
---------------------------------------------------------------------------------------------
01             Province1        3                    0              0
02             Province2        1                    1              0
03             Province3        1                    0              1   

I have try to make query using subquery like bellow, but it's not work :

select id_province,(select count(*) from transaction where id_grade='A') as count of grade A,(select count(*) from transaction where id_grade='B') as count of grade B,
    (select count(*) from transaction where id_grade='C') as count of grade C group by id_province

moreover, query above show data like bellow :(

id_province | province  | count of grade A  | count of grade B | count of grade C
---------------------------------------------------------------------------------------------
01             Province1        3                    0              0
02             Province2        3                    0              0
03             Province3        3                    0              0 

Any idea how to solve this ?


Aucun commentaire:

Enregistrer un commentaire