📈 Lesson 6: SQL Aggregation (Summarizing Data)

⏱️ Estimated time: 25 minutes | Difficulty: Intermediate

Aggregate Functions

SELECT COUNT(*) FROM users;          -- Total rows
SELECT COUNT(email) FROM users;      -- Non-null emails
SELECT SUM(amount) FROM orders;      -- Total of all amounts
SELECT AVG(age) FROM users;          -- Average age
SELECT MAX(amount) FROM orders;      -- Highest amount
SELECT MIN(amount) FROM orders;      -- Lowest amount

GROUP BY

-- Count users per city
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

-- Total sales per product
SELECT product, SUM(amount) AS total_sales
FROM orders
GROUP BY product
ORDER BY total_sales DESC;

HAVING (Filter Groups)

WHERE filters rows, HAVING filters groups after GROUP BY.

-- Cities with more than 5 users
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 5;

-- Products with total sales over $1000
SELECT product, SUM(amount) AS total
FROM orders
GROUP BY product
HAVING SUM(amount) > 1000;