CODEWITHSUNDEEP
androidmysqlsqlite
redGREENblue
redGREENblue

Reputation: 3126

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.

Upvotes: 0

Views: 39

Answers (1)

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

Related Questions