GROUP BY - grouping data

So far we have counted all books at once. But what if we want to count them separately for each category? Here enters the most powerful spell of this chamber - GROUP BY.

What is grouping?

GROUP BY splits rows into groups that share the same value in a chosen column, and then lets you compute an aggregate for each group separately.

Imagine taking all the scrolls from a shelf and arranging them into separate piles - one pile per category. Then you count each pile on its own.

1SELECT kategoria_id, COUNT(*)
2FROM ksiazki
3GROUP BY kategoria_id;

This means: "For each category give its number and the number of books". The result is one row per category:

| kategoria_id | COUNT(*) | |--------------|----------| | 1 | 42 | | 2 | 18 | | 3 | 90 |

The rule: SELECT and GROUP BY

This is an important rule: only the following may appear in the SELECT list:

• columns listed in GROUP BY (here:

  kategoria_id

  ),
• and aggregate functions (here:

  COUNT(*)

  ).

Why? Because each result row represents a whole group. The question "what is the title of this group" makes no sense - a group contains many titles.

Other grouping examples

1-- Average book price in each category
2SELECT kategoria_id, AVG(cena)
3FROM ksiazki
4GROUP BY kategoria_id;

1-- How many authors come from each country
2SELECT kraj, COUNT(*)
3FROM autorzy
4GROUP BY kraj;

GROUP BY turns one huge shelf into a clear report. It is the heart of data analysis!