The Hall of Numbers - summary

Wonderful, @name! 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:

1SELECT   column, COUNT(*)
2FROM     table
3WHERE    row_condition
4GROUP BY column
5HAVING   aggregate_condition
6ORDER BY COUNT(*) DESC;

An example combining everything

1SELECT kategoria_id, COUNT(*), AVG(cena)
2FROM ksiazki
3WHERE dostepna = 1
4GROUP BY kategoria_id
5HAVING COUNT(*) > 5
6ORDER 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!