Lesson 6 of 10
Grouping rows
- 12 min
- 5 checks
- 3 hints
- Go to task
Goal
The GROUP BY clause
01
Explanation
GROUP BY city splits the rows into groups by city, and then the aggregate functions run on each group separately, so you get one row per city instead of one row for the whole table.
For example, the question "how many orders are there in each city?" becomes SELECT city, COUNT(*) FROM orders GROUP BY city.
Keep in mind that every column in SELECT has to either appear in GROUP BY or sit inside an aggregate function. If you added customer, SQLite would give you a name from some row in the group without any warning, while PostgreSQL would reject the query.
02
Your task
For each city, show city, orders_count for its number of orders and revenue for the total of its amounts, sorted from the highest revenue to the lowest.
Checks
5 checks- Columns are city, orders_count and revenue
- One row per city
- The order count is right for each city
- The revenue is right for each city
- Sorted by revenue, highest first
Tables in this lesson
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer TEXT NOT NULL,
city TEXT NOT NULL,
amount INTEGER NOT NULL,
rating INTEGER
);
INSERT INTO orders (customer, city, amount, rating) VALUES
('Layan', 'Riyadh', 120, 5),
('Khalid', 'Jeddah', 300, 4),
('Layan', 'Riyadh', 80, NULL),
('Reem', 'Dammam', 45, 3),
('Sara', 'Riyadh', 210, 5),
('Khalid', 'Jeddah', 50, NULL),
('Fahad', 'Jeddah', 150, 2),
('Noura', 'Riyadh', 95, 4),
('Omar', 'Mecca', 400, NULL),
('Layan', 'Riyadh', 35, 4),
('Reem', 'Dammam', 130, NULL),
('Sara', 'Riyadh', 70, 3);