SQL Count of Column value and its a subColumn

Question

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)

I removed the incompatible database tags. Please tag the question with the database you are really using. — Gordon Linoff, Sep 26 '16 at 14:12
In the given data [per given as text vs a DML INSERT], is each *invisible* value suppose to be the empty-string or the NULL value? — CRPence, Sep 28 '16 at 22:16

score 4 · Accepted Answer · edited Sep 26 '16 at 14:22

4

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

edited Sep 26 '16 at 14:22

Gordon Linoff

1,242,037
58
646
786

answered Sep 26 '16 at 14:17

Christian

827
6
14

Just a FollowUp Question can we also Use Case to get Value such as : Count where isResolved!='y' and IsAssigned !='Y' – Jashanpreet Singh Sep 26 '16 at 14:32
@JashanPreet - yes – Hogan Sep 26 '16 at 14:33

score 3 · Answer 2 · edited Sep 19 '17 at 07:05

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.

score 0 · Answer 3 · edited May 23 '17 at 12:19

0

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

edited May 23 '17 at 12:19

Community

1
1

answered Sep 26 '16 at 14:42

Jorge Guerrero

323
3
5

There are two answers that are functionally the same and more correct given the requirements that were posted over a half hour ago -- why did you post this answer? – Hogan Sep 26 '16 at 14:43

score 0 · Answer 4 · answered Sep 28 '16 at 22:41

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

SQL Count of Column value and its a subColumn

4 Answers4