vendredi 2 octobre 2015

Group by in sub query

Here's the table in question,

enter image description here

What I am trying to do is to get the count of entries segregated by type for each person(claimed_by). For example, for example,

John : Total entries :4 , Type 1 entries: 1, Type 2 entries : 3.

Jane : Total entries :4 , Type 1 entries: 4, Type 2 entries : 0.

Here's what I have at the moment,

SELECT count(id) as total_entry,claimed_by,
(select count(id) from tblIssues where type=1) as type1_entry,
(select count(id) from tblIssues where type=2) as type2_entry 
FROM tblIssues 
GROUP BY claimed_by 
order by count(id) desc

This return correct value for total_entry but incorrect for type1 and type2. I think this is because I am not doing a group_by in the sub query but I am not sure how to correctly do that.

Aucun commentaire:

Enregistrer un commentaire