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
:
<!-- language: lang-none -->
<pre><code>| <b>Letters</b> |
|---------|
| A |
| A |
| B |
| C |</code></pre>
We can test out each Aggregate counter syntax like this (Interactive Demo in SQL Fiddle):
<!-- language: lang-sql -->
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):
<!-- language: lang-none -->
<pre><code>| <b>Description</b> | <b>Counts</b> |
|-------------------|--------|
| Count - End | 2 |
| Count - Else Null | 2 |
| Count - Else Zero | 4 | *<b>Note</b>: Will include <i>count</i> of <i>zero</i> values
| Sum - End | 2 |
| Sum - Else Null | 2 |
| Sum - Else Zero | 2 |</code></pre>
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.