SQL and MySQL course Β· Module 6: Aggregation and grouping

The Hall of Numbers - summary

2 min read Β·
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?

  1. VideoModule summary

Useful articles