Aggregating with GROUP BY

Aggregate functions let you compute summary statistics across rows. GROUP BY lets you apply those aggregations to groups of data.

Aggregate Functions

Function	Description	Example
COUNT()	Count rows	COUNT(*), COUNT(column)
SUM()	Total of values	SUM(price)
AVG()	Average value	AVG(price)
MIN()	Smallest value	MIN(price)
MAX()	Largest value	MAX(price)

COUNT

-- How many products do we have?
SELECT COUNT(*) AS total_products FROM products;

-- COUNT(*) counts all rows; COUNT(column) skips NULLs
SELECT COUNT(email) AS users_with_email FROM users;

SUM, AVG, MIN, MAX

SELECT
  SUM(price) AS total_value,
  AVG(price) AS average_price,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products;

Result:

total_value | average_price | cheapest | most_expensive
------------+---------------+----------+---------------
2214.93     | 316.42        | 4.99     | 1299.99

GROUP BY

GROUP BY divides rows into groups and applies aggregate functions to each group:

-- Count products per category
SELECT
  category,
  COUNT(*) AS product_count
FROM products
GROUP BY category;

Result:

category    | product_count
------------+--------------
Electronics | 3
Furniture   | 2
Office      | 2

-- Average price per category
SELECT
  category,
  ROUND(AVG(price), 2) AS avg_price,
  COUNT(*) AS num_products
FROM products
GROUP BY category
ORDER BY avg_price DESC;

Result:

category    | avg_price | num_products
------------+-----------+-------------
Electronics | 476.66    | 3
Furniture   | 374.99    | 2
Office      | 17.49     | 2

GROUP BY Rules

Important: Every column in SELECT must be either:

1. In the GROUP BY clause, OR
2. Inside an aggregate function

-- ✅ CORRECT
SELECT category, COUNT(*) FROM products GROUP BY category;

-- ❌ ERROR: 'name' is not in GROUP BY or an aggregate function
SELECT name, category, COUNT(*) FROM products GROUP BY category;

HAVING — Filtering Groups

WHERE filters rows before grouping. HAVING filters groups after aggregation:

-- Categories with more than 1 product
SELECT
  category,
  COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 1;

-- Categories where average price exceeds $200
SELECT
  category,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 200;

WHERE vs HAVING

-- WHERE filters rows first (before grouping)
-- HAVING filters groups after aggregation