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.

Answers


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

Need Your Help

How do I "unzip" a gzipped stream while reading it

c++ stream gzip

My program is using HTTP to read gzipped files over the network.

All possible combinations from given sets but without repetition of sets’ internal elements

c# linq collections combinatorics

I have some fixed sets of integers, with distict increasing values in each a set, for example: