Group by in sub query
Here's the table in question,
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.
You can put a condition in the sum(). It adds up how many times it is true (1).
SELECT claimed_by, count(id) as total_entry, sum(type = 1) as type1_entry, sum(type = 2) as type2_entry, sum(type = 3) as type3_entry FROM tblIssues GROUP BY claimed_by order by count(id) desc