FILTER inside an aggregate in SQL

One pass over the table, several differently filtered counts.

SELECT
    customer_id,
    COUNT(*) AS orders_total,
    COUNT(*) FILTER (WHERE status = 'paid') AS paid,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    SUM(total) FILTER (WHERE status = 'paid') AS paid_value
FROM orders
GROUP BY customer_id;

How it works

1. FILTER (WHERE ...) restricts the rows one aggregate sees.
2. The outer WHERE would drop the rows for every column at once.
3. SUM(CASE WHEN ... THEN 1 END) is the older spelling of the same idea.

Keywords and builtins used here

• AS
• BY
• COUNT
• FROM
• GROUP
• SELECT
• SUM
• WHERE

The run, in numbers

Lines

8

Characters to type

239

Tokens

57

Three-star pace

95 tpm

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

Type this snippet

Step 3 of 7 in Expressions, step 18 of 24 in Analytics & reporting.

← Previous Next →