Counting unique values in SQL

COUNT DISTINCT answers how many different values a column holds.

SELECT
    DATE(occurred_at) AS day,
    COUNT(*) AS events,
    COUNT(DISTINCT session_id) AS sessions,
    SUM(CASE WHEN kind = 'error' THEN 1 ELSE 0 END) AS errors
FROM events
GROUP BY DATE(occurred_at)
ORDER BY day;

How it works

1. COUNT(DISTINCT col) counts unique non-NULL values.
2. A filtered SUM of a boolean expression counts a subset.
3. Dividing two counts gives a rate per group.

Keywords and builtins used here

• AS
• BY
• CASE
• COUNT
• DATE
• DISTINCT
• ELSE
• END
• FROM
• GROUP
• ORDER
• SELECT
• SUM
• THEN
• WHEN
• day

The run, in numbers

Lines

8

Characters to type

203

Tokens

50

Three-star pace

80 tpm

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

Type this snippet

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

← Previous Next →