Jashan Preet Jashan Preet - 2 months ago 8
SQL Question

SQL Count of Column value and its a subColumn

I have A Table in DB2 Database such as below:

StatusCode | IsResolved | IsAssigned
ABC | Y |
ABC | N |
ABC | |
ADEF | Y |
ADEF | | Y


I want to get data in the way such as:

StatusCode |Count of Status Code| Count of Resolved with value Y| Count of Assigned With value Y
ABC | 3 | 1 | 0
ADEF | 2 | 1 | 1


I am able to get count of Status Code by using
groupBy
but I am not sure how to fetch data of count of resolved and assigned in the same query.

Query: select statusCode,count(statusCode) from table group by statusCode


Can anyone help me in how to fetch the resolved and Assigned count?

Issue Solution: Christian and JPW: Solution was to Use sum(case IsResolved when 'Y' then 1 else 0 end)

Answer

Try to use

 select statusCode, count(statusCode),
        sum(case IsResolved when 'Y' then 1 else 0 end),
        sum(case IsAssigned when 'Y' then 1 else 0 end) 
 from table
 group by statusCode 
Comments