HAVING filters groups in SQL

WHERE filters rows before grouping; HAVING filters the groups after.

SELECT
    customer_id,
    COUNT(*) AS orders,
    SUM(total) AS lifetime_value
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3
   AND SUM(total) > 500
ORDER BY lifetime_value DESC;

How it works

1. HAVING can reference aggregates, WHERE cannot.
2. Use both together: narrow the rows, then narrow the groups.
3. The clause order is WHERE, GROUP BY, HAVING, ORDER BY.

Keywords and builtins used here

• AND
• AS
• BY
• COUNT
• DESC
• FROM
• GROUP
• HAVING
• ORDER
• SELECT
• SUM
• WHERE

The run, in numbers

Lines

10

Characters to type

195

Tokens

45

Three-star pace

85 tpm

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

Type this snippet

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

← Previous Next →