using sql count in a case statement

using sql count in a case statement

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

Close… try:

select 
   Sum(case when rsp_ind = 0 then 1 Else 0 End) as New,
   Sum(case when rsp_ind = 1 then 1 else 0 end) as Accepted
from tb_a

using sql count in a case statement

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

For example, heres 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.

Leave a Reply

Your email address will not be published.