Grouping on several columns in SQL

Group by two columns and every distinct pair becomes a row.

SELECT
    country,
    plan,
    COUNT(*) AS customers,
    ROUND(AVG(monthly_total), 2) AS avg_bill
FROM subscriptions
GROUP BY country, plan
HAVING COUNT(*) >= 5
ORDER BY country, customers DESC;

How it works

1. The grouped columns are the only ones you may select unaggregated.
2. Ordering by the grouped columns makes the report readable.
3. Add a column to GROUP BY and the counts get smaller, never larger.

Keywords and builtins used here

• AS
• AVG
• BY
• COUNT
• DESC
• FROM
• GROUP
• HAVING
• ORDER
• SELECT

The run, in numbers

Lines

9

Characters to type

182

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 2 of 4 in Grouping, step 6 of 22 in Joins & aggregation.

← Previous Next →