Aggregation functions

Common analytics aggregations:

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

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

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;

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

HAVINGHAVING 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;

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 BYGROUP BY.
• WHEREWHERE filters rows before grouping; HAVINGHAVING filters after.

Mental model: split → aggregate → combine

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

🧪 Try It Yourself

Exercise 1 – COUNT and SUM

Exercise 2 – GROUP BY a dimension

Exercise 3 – HAVING filters groups

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.