Free SQL queries lessonsSQL queries · Stage 2 · Counting and grouping · Step 2

One answer per group

Split rows into groups with GROUP BY and summarise each group. About 9 minutes.

For each, one row

"How many students are there?" is one number. "How many students in each branch?" is one number per branch. That "in each" is GROUP BY.

GROUP BY branch puts all rows with the same branch into one group. Then every aggregate in SELECT is worked out inside each group, and each group gives one row of the answer.

GROUP BY col: one answer row for each different value.

In code

SELECT branch, COUNT(*) AS students
FROM students
GROUP BY branch;

Students and average cgpa for each branch

students

name	branch	cgpa
Asha	CSE	8
Bilal	ECE	9
Chetan	CSE	9
Divya	ME	7
Eshan	ECE	6
Farah	CSE	7

GROUP BY branch: three rows are CSE, so they form one group.

Move 1 of 6

What can go in SELECT

After grouping, a row of the answer stands for a whole group. So SELECT can show the grouping columns (the same in every row of the group) and aggregates. A plain column like name has many values in a group, and SQL can't know which one you want.

SQLite lets that run and shows an arbitrary one. Postgres and MySQL in strict mode refuse. Either way it is a bug. WHERE filters rows before they are grouped, and you can group by more than one column.

Selected column? grouped, or inside an aggregate.

In code

SELECT branch, year, COUNT(*)
FROM students
WHERE cgpa >= 6
GROUP BY branch, year;

Quick check

SELECT branch, name, COUNT(*) FROM students GROUP BY branch. What is wrong with this query?

Aname isn't grouped or aggregated, so there's no single name for a branch
BCOUNT(*) can't be used with GROUP BY
Cbranch has to be in quotes

Show the answer

name isn't grouped or aggregated, so there's no single name for a branch. A group has many names. SQLite picks one quietly, Postgres refuses.

Your problem

Orders per city

The orders table has one row per order. For each city show its number of orders as orders and the total amount as revenue. Order by city.

Tables

orders(id, customer, city, amount)

Up to 2,000 orders

Write the query in your browserFree account, no card. Your query runs on real tables, with hints if you get stuck, and your progress is saved.