CODEWITHSUNDEEP
sqlgroup-bydb2
Jashanpreet Singh
Jashanpreet Singh

Reputation: 122

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)

Upvotes: 0

Views: 408

Answers (4)

jpw
jpw

Reputation: 44881

One way to get the result you want is to use conditional aggregation (where you use a predicate to determine how to aggregate data) like this:

select 
  StatusCode, 
  count(*) as "Count of Status Code",
  sum(case when IsResolved = 'Y' then 1 else 0 end) as "Count of Resolved with value Y",
  sum(case when IsAssigned = 'Y' then 1 else 0 end) as "Count of Assigned With value Y"
from your_table
group by StatusCode;

The case expression construct (case ... when ... then .. end) is part of the ANSI SQL standard, so this should work in any compliant database.

Upvotes: 3

CRPence
CRPence

Reputation: 1259

I suppose the prior answers used the SUM aggregate because the value of the missing values was unknown. If the missing values are the NULL value, then each could have been coded as the COUNT with the same effect as the SUM.
And if the missing values from the "I have a table" given in the OP are the NULL value, and if [effectively the data meets or actually there exists] a CHECK constraint for the isColumnNames of IN ('Y','N'), then similar to the other answers, but performing a COUNT and using NULLIF as a simplified/special-case effect of the CASE expression:

 select
   statuscode                    as "StatusCode"
 , count(*)                      as "Count of Status Code"
 , count(nullif(isResolved,'N')) as "Count of Resolved with value Y"
 , count(nullif(isAssigned,'N')) as "Count of Assigned with value Y"
 from so39705143    
 group by statuscode
 order by statuscode

Upvotes: 0

Jorge Guerrero
Jorge Guerrero

Reputation: 343

You can achieve this using SUM() and CASE

SELECT 
statusCode,
COUNT(statusCode)
,SUM(CASE WHEN IsResolved='Y' THEN 1 ELSE 0 END) Resolved
,SUM(CASE WHEN IsAssigned='Y' THEN 1 ELSE 0 END) Assigned
FROM [Questions] GROUP BY statusCode

Here is a related question: Sql Server equivalent of a COUNTIF aggregate function

Upvotes: 0

Christian
Christian

Reputation: 827

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

Upvotes: 4

Related Questions