CODEWITHSUNDEEP
sqlt-sql
Bharat
Bharat

Reputation: 6095

Group by with case when

I have Projects - Issues, one to many relationship.

I want Pending issues and Completed issues for each project.

So, what I have done

SELECT
    a.id ,
    a.Name, 
    SUM(CASE WHEN b.StatusId = 3 THEN 1 ELSE NULL END) AS CompletedIssues,
    SUM(CASE WHEN b.StatusId != 3 THEN 1 ELSE NULL END) AS PendingIssues
FROM 
Projects a
    JOIN Issues b
        ON a.ID = b.ProjectId
GROUP BY 
a.name, 
b.StatusId, 
a.ID

But it's not giving proper output. see below snap.

enter image description here

There are two separate rows for Completed and pending issues and sometimes more then 2 rows based upon Issues Status ID (See BT5).

Is case when is wrong for this scenario?

what is the proper way to achieve this?

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Fix your group by:

select p.id, p.Name, 
       sum(case when i.StatusId = 3 then 1 else null end) as CompletedIssues,
       sum(case when i.StatusId <> 3 then 1 else null end) as PendingIssues
from Projects p join
     Issues i
     on p.ID = i.ProjectId
group by p.name, p.id;

Note: You may not want else NULL. Normally, you want counts to be zero rather than NULL:

select p.id, p.Name, 
       sum(case when i.StatusId = 3 then 1 else 0 end) as CompletedIssues,
       sum(case when i.StatusId <> 3 then 1 else 0 end) as PendingIssues
from Projects p join
     Issues i
     on p.ID = i.ProjectId
group by p.name, p.id;

Also, I changed the table aliases to something more meaningful. Don't use meaningless letters such as a and b. Use table abbreviations.

Upvotes: 2

Related Questions