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()

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

GROUP BY

Group by a dimension:

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).

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.

  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"]

Check your understanding

🧪 Try It Yourself

Exercise 1 – COUNT and SUM

Press Run to see what your code prints.

Exercise 2 – GROUP BY a dimension

Press Run to see what your code prints.

Exercise 3 – HAVING filters groups

Press Run to see what your code prints.

Next

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.