SQL GROUP BY

Overview

GROUP BY collapses rows sharing the same values in specified columns, enabling aggregate functions. Use HAVING to filter groups after aggregation—WHERE filters rows before grouping.

Syntax / Usage

SELECT
  category,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price,
  SUM(stock) AS total_stock
FROM products
WHERE active = true
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY product_count DESC;

-- Group by multiple columns
SELECT user_id, DATE(created_at), COUNT(*)
FROM orders
GROUP BY user_id, DATE(created_at);

Common aggregates: COUNT, SUM, AVG, MIN, MAX, STRING_AGG (Postgres).

Examples

Monthly revenue:

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS revenue
FROM payments
WHERE status = 'completed'
GROUP BY 1
ORDER BY 1;

Top customers by spend:

SELECT u.email, SUM(o.total) AS spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY spent DESC
LIMIT 10;

Common Mistakes

• Selecting non-aggregated columns not in GROUP BY (invalid in strict SQL)
• Using WHERE for aggregate conditions—use HAVING instead
• COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column) confusion
• Double counting from joins before grouping—consider subqueries or distinct counts

See Also

select joins where-clause indexes