Skip to content
Python Central Hub

Aggregations (COUNT, SUM, AVG) and GROUP BY

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()
Total orders and revenue
SELECT
  COUNT(*) AS orders,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order_value
FROM orders;
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;
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

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

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did