SQL and MySQL course Β· Module 6: Aggregation and grouping
The Hall of Numbers - summary
In this lesson4
Wonderful! You have passed through the Hall of Numbers and learned to look at data like a true steward of the Library - from a bird's eye view. Here is your scroll of knowledge:
What you have learned
- Aggregate functions return a single value from many rows: COUNT, SUM, AVG, MIN, MAX.
- COUNT(*) counts all rows, COUNT(column) skips NULL values, and COUNT(DISTINCT column) counts only distinct values.
- GROUP BY splits rows into groups and computes an aggregate for each group separately.
- HAVING filters groups by an aggregate result (e.g.
HAVING COUNT(*) > 3). - WHERE filters rows before grouping, while HAVING filters groups after aggregation.
Order of clauses - remember it forever
The clauses must always appear in this order:
SELECT column, COUNT(*)
FROM table
WHERE row_condition
GROUP BY column
HAVING aggregate_condition
ORDER BY COUNT(*) DESC;An example combining everything
SELECT kategoria_id, COUNT(*), AVG(cena)
FROM ksiazki
WHERE dostepna = 1
GROUP BY kategoria_id
HAVING COUNT(*) > 5
ORDER BY AVG(cena) DESC;"For available books, show the categories with more than 5 titles, give their count and average price, and sort the result from the most expensive."
What's next?
In the next location you will learn to connect shelves - you will meet JOINs, which let you combine books with their authors and categories into one coherent catalog. The abacus is set aside - time for bridges between tables!
Spotted a mistake in this lesson?