Lesson 7 of 10
Filtering groups
- 12 min
- 4 checks
- 3 hints
- Go to task
Goal
HAVING versus WHERE
01
Explanation
WHERE filters rows before grouping, and HAVING filters groups after it. A condition about a single order belongs in WHERE, and a condition about the result of an aggregate belongs in HAVING.
That's why WHERE COUNT(*) > 3 fails: nothing has been counted yet when WHERE runs. The right way to write it is HAVING COUNT(*) > 3.
The opposite mistake is more dangerous, because SQLite won't warn you about it. If you put a row condition in HAVING, the row isn't left out of the count, and you get wrong numbers without any error message.
02
Your task
Show city and orders_count for the cities with two or more orders, without counting orders whose amount is under 50.
Checks
4 checks- Columns are city and orders_count
- Every city has at least two orders
- Orders under 50 are left out of the count, and an order of exactly 50 is counted
- Every matching city appears, and no others
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);