SQL from zero · Chapter 7 of 37

Grouping things

By the end of this chapter you can count per group: posts of each type, posts each month, members in each city, all in one query. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Counting things
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

Last chapter's queries give one number for the whole table: 121 posts. Meera is preparing a product review, and one number is not enough. She needs breakdowns: how many posts of each type, how many each month, which cities have the most members.

With what you know, you would write one query per type (WHERE type = 'text', then 'image', then 'video'...), run each one, and copy the numbers into a table by hand. And the day someone adds a new type, your list silently misses it.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    posts-per-type.sql: posts of each type

    For each type of post (text, image, video and so on), count how many posts have that type. Count only posts that are not deleted.

    Return two columns: type and posts. Any order.

  2. Task 2

    city-status.sql: members by city and status

    For each city and status pair that exists (Pune and online, Pune and away, ...), count how many members have it. Leave out members who left. Members with no city still count: they form their own groups with an empty city.

    Return three columns: city, status and members. Any order.

  3. Task 3

    posts-per-month.sql: posts per month

    Count the posts that are not deleted, month by month. Write each month as text like 2026-01, and list the oldest month first. Only months that have posts appear.

    Return two columns: month and posts.

  4. Task 4 · interview question

    Interview: busy-cities.sql, cities with at least 2 members

    Find the cities where at least 2 members live, with how many. Do not count members who left, and leave out members with no city.

    Return two columns: city and members. Most members first; when two cities have the same number, sort them A to Z.

What you will learn

  • GROUP BY: sort the rows into piles, then count each pile, so one query returns a row per type, per month or per city.
  • The rules of grouping: what you can and cannot select once rows are grouped.
  • WHERE and HAVING: filtering rows before they are grouped, and groups after they are counted.

More in Counting things

Build "Grouping things" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.