Aggregations (COUNT, SUM, AVG) and GROUP BY

Aggregation functions

Common analytics aggregations:

COUNT(*): number of rows
COUNT(DISTINCT user_id): unique users
SUM(amount): total revenue
AVG(amount): average order amount
MIN(), MAX()

Total orders and revenue

SELECT
  COUNT(*) AS orders,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order_value
FROM orders;

GROUP BY

Group by a dimension:

Revenue by country

SELECT
  u.country,
  COUNT(*) AS orders,
  SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.user_id = o.user_id
GROUP BY u.country
ORDER BY revenue DESC;

HAVING

HAVING filters groups (after aggregation).

Only countries with 100+ orders

SELECT
  u.country,
  COUNT(*) AS orders
FROM orders o
JOIN users u ON u.user_id = o.user_id
GROUP BY u.country
HAVING COUNT(*) >= 100
ORDER BY orders DESC;

Tip: avoid common mistakes

Every selected non-aggregated column must be in GROUP BY.
WHERE filters rows before grouping; HAVING filters after.

Mental model: split → aggregate → combine

GROUP BY works the same way in every SQL dialect: rows are split into buckets by the grouping column, an aggregate function collapses each bucket to one number, and the buckets are combined back into a result table — one row per group.

diagram GROUP BY pipeline mermaid

  flowchart LR
  A["All rows"] --> B{"GROUP BY country"}
  B --> C["Bucket: IN"]
  B --> D["Bucket: US"]
  C --> E["SUM(amount) per bucket"]
  D --> E
  E --> F["One row per group"]

Rows are split into buckets by the grouping column, each bucket is collapsed by an aggregate, then combined into one row per group.

sketch Rows collapsing into grouped totals p5.js

Each colored dot is one order row; rows with the same country collapse into a single aggregated bar.

🧪 Try It Yourself

Exercise 1 – COUNT and SUM

Exercise 2 – GROUP BY a dimension

Exercise 3 – HAVING filters groups

Now that you can summarize one table, Joins (INNER, LEFT) for Analytics shows you how to bring columns from other tables into that same aggregation.

pch.coffeeTagline

pch.coffeeCta

pch.feedbackHeading

pch.feedbackSubheading