using sql count in a case statement

49

I have a table and i need to present the output in the following fashion.

tb_a:

col1  |  reg_id | rsp_ind 

Count of rows with rsp_ind = 0 as 'New' and 1 as 'Accepted'

The output should be

NEW | Accepted
9   | 10

I tried using the following query.

select 
  case when rsp_ind = 0 then count(reg_id)end as 'New',
  case when rsp_ind = 1 then count(reg_id)end as 'Accepted'
from tb_a

and i m getting output as

NEW | Accepted
NULL| 10
9   | NULL

Could someone help to me tweak the query to achieve the output. Note : I cannot add a sum surrounding this. Its part of a bigger program and so i cannot add a super-query to this.

This question is tagged with sql

~ Asked on 2013-07-31 15:58:30

The Best Answer is


117

SELECT 
    COUNT(CASE WHEN rsp_ind = 0 then 1 ELSE NULL END) as "New",
    COUNT(CASE WHEN rsp_ind = 1 then 1 ELSE NULL END) as "Accepted"
from tb_a

You can see the output for this request HERE

~ Answered on 2013-07-31 16:01:02


10

Depending on you flavor of SQL, you can also imply the else statement in your aggregate counts.

For example, here's a simple table Grades:

| Letters |
|---------|
| A       |
| A       |
| B       |
| C       |

We can test out each Aggregate counter syntax like this (Interactive Demo in SQL Fiddle):

SELECT
    COUNT(CASE WHEN Letter = 'A' THEN 1 END)           AS [Count - End],
    COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END) AS [Count - Else Null],
    COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)    AS [Count - Else Zero],
    SUM(CASE WHEN Letter = 'A' THEN 1 END)             AS [Sum - End],
    SUM(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END)   AS [Sum - Else Null],
    SUM(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)      AS [Sum - Else Zero]
FROM Grades

And here are the results (unpivoted for readability):

|    Description    | Counts |
|-------------------|--------|
| Count - End       |    2   |
| Count - Else Null |    2   |
| Count - Else Zero |    4   | *Note: Will include count of zero values
| Sum - End         |    2   |
| Sum - Else Null   |    2   |
| Sum - Else Zero   |    2   |

Which lines up with the docs for Aggregate Functions in SQL

Docs for COUNT:

COUNT(*) - returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) - evaluates expression for each row in a group, and returns the number of nonnull values.
COUNT(DISTINCT expression) - evaluates expression for each row in a group, and returns the number of unique, nonnull values.

Docs for SUM:

ALL - Applies the aggregate function to all values. ALL is the default.
DISTINCT - Specifies that SUM return the sum of unique values.

~ Answered on 2018-08-30 20:54:41


Most Viewed Questions: