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