# 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.