GROUP BY and HAVING in PostgreSQL

GROUP BY collapses rows that share common values into summary rows, which you then analyze with aggregate functions. HAVING filters those groups, much like WHERE filters individual rows.

Basic GROUP BY

-- Count orders per status
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

-- Revenue by product category
SELECT
  p.category,
  SUM(oi.quantity * oi.unit_price) AS revenue,
  COUNT(DISTINCT o.id) AS total_orders
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
ORDER BY revenue DESC;

Every column in the SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function like COUNT, SUM, or AVG.

Filtering Groups with HAVING

-- Only categories with more than $1000 in revenue
SELECT
  p.category,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 1000;

-- Customers with 5 or more orders
SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5;

WHERE runs before grouping and cannot reference aggregates. HAVING runs after grouping and is specifically designed to filter on aggregate values.

Grouping by Expressions

-- Orders per month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

Common Use Cases

• Sales reports grouped by product, region, or time period
• Finding customers who exceed usage thresholds
• Dashboard widgets showing counts by category or status
• Detecting anomalies such as users with unusually high activity