CODEWITHSUNDEEP
mysqlsqlsqlite
Uday
Uday

Reputation: 1484

Need help DB Query for this scenario

I am unable to derive a SQL query for the following table content.

enter image description here

When i tried below query i am getting above said output. Can someone help me to give the required query for it.

select Name, count(Status) from mytable where Status='Open' group by mytable union
select Name, count(Status) from mytable where Status='Cleared' group by mytable

Upvotes: 0

Views: 36

Answers (1)

jarlh
jarlh

Reputation: 44786

Use case expressions in the select list to do conditional aggregation. 

select Name,
       count(case when Status = 'Open' then 1 end) as opencnt,
       count(case when Status = 'Cleared' then 1 end) as clearedcnt
from mytable
where Status in ('Open', 'Cleared')
group by Name

COUNT() counts non-null values. The case expressions above returns null when the conditions aren't fulfilled.

Upvotes: 2

Related Questions