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

GROUP BY - grouping data

2 min read Β·
In this lesson3

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.

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

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

kategoria_idCOUNT(*)
142
218
390

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

-- Average book price in each category
SELECT kategoria_id, AVG(cena)
FROM ksiazki
GROUP BY kategoria_id;
-- How many authors come from each country
SELECT kraj, COUNT(*)
FROM autorzy
GROUP BY kraj;

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

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What is the GROUP BY clause used for?

  2. 2. Which columns may appear in the SELECT list when using GROUP BY?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a query that, for each category (the kategoria_id column), counts the number of books. Select the kategoria_id column and COUNT(*), and group the result by kategoria_id.

  • Vertical ordering

    Arrange the clauses of a simple grouping query in the correct order (top to bottom):

  • Horizontal ordering

    Arrange the tokens on a single line to form the SELECT list that counts authors grouped by country:

  • Click in order

    Click the tokens in order to build a query that computes the average book price in each category:

  • Horizontal ordering

    Arrange the tokens on a single line to form a clause that groups by category:

  1. QuestionWhat GROUP BY is for
  2. QuestionSELECT and GROUP BY
  3. Coding taskBooks in each category
  4. Put in orderClauses of a simple grouping
  5. Put in orderNumber of authors in each country
  6. QuestionWhat a result row represents
  7. Click in orderAverage price in each category
  8. Put in orderGrouping by kategoria_id

Useful articles