Aggregate Functions
COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING
Skills taught: sql.count, sql.sum-avg-min-max, sql.group-by, sql.having
Aggregate functions
-- COUNT(*) — number of rows
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(*) AS total_orders FROM orders;
-- COUNT(col) — counts non-NULL values
SELECT COUNT(email) AS users_with_email FROM users;
-- SUM — total
SELECT SUM(total) AS total_revenue FROM orders;
SELECT SUM(quantity) AS total_items_sold FROM orders;
-- AVG — average
SELECT AVG(price) AS avg_price FROM products;
-- MIN and MAX
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;
-- Multiple aggregates in one query
SELECT
COUNT(*) AS total_products,
ROUND(AVG(price), 2) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price,
SUM(stock) AS total_stock
FROM products;
GROUP BY and HAVING
-- Count products per category
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;
-- Average price per category
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;
-- Total orders per user
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;
-- HAVING: filter groups (like WHERE but for groups)
-- Users who spent more than 1000 total
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;
-- WHERE filters rows BEFORE grouping
-- HAVING filters groups AFTER grouping
SELECT category, COUNT(*) AS product_count
FROM products
WHERE stock > 50 -- filter rows first
GROUP BY category
HAVING product_count >= 2; -- then filter groups
Frequently Asked Questions
Why does COUNT(*) and COUNT(column) give different results?
COUNT(*) counts every row, including rows where the column is NULL. COUNT(column) counts only rows where that specific column is NOT NULL. If your column has 3 NULL values in 10 rows: COUNT(*) = 10, COUNT(column) = 7.
Can I use a WHERE condition and a HAVING condition in the same query?
Yes! Use WHERE to filter individual rows first (before grouping), then HAVING to filter the resulting groups. Example: WHERE price > 100 filters out cheap products, then GROUP BY category, HAVING COUNT(*) > 2 keeps only categories with more than 2 remaining products.