What COUNT counts in SQL

COUNT(*) counts rows; COUNT(column) counts values. NULLs decide the difference.

SELECT
    COUNT(*) AS rows_total,
    COUNT(shipped_at) AS shipped,
    COUNT(*) - COUNT(shipped_at) AS not_yet_shipped,
    COUNT(DISTINCT customer_id) AS customers
FROM orders;

How it works

COUNT(*) counts every row in the group, NULLs included.
COUNT(col) skips rows where that column is NULL.
Subtracting the two gives you the number of missing values.

Keywords and builtins used here

AS
COUNT
DISTINCT
FROM
SELECT

The run, in numbers

Lines: 6
Characters to type: 163
Tokens: 37
Three-star pace: 80 tpm

At the three-star pace of 80 tokens a minute, this run takes about 28 seconds.

Type this snippet

Step 3 of 4 in Aggregation, step 3 of 22 in Joins & aggregation.

← Previous Next →