Percuma untuk mula. RM 25/bulan untuk seluruh bank.

Step 1: Filter before you count

SELECT id, city, status FROM rides WHERE status = 'completed' ORDER BY city, id;

Nine rides in, six out. The three cancellations — including Ipoh's only ride — are gone before anything is counted. Ipoh dropping out entirely is not an accident to fix later; it's the correct answer to "every city that has at least one completed ride."

Step 2: Group the survivors and count

SELECT city, COUNT(*) AS completed_rides
FROM rides
WHERE status = 'completed'
GROUP BY city;

GROUP BY city folds the six rows into three buckets; COUNT(*) runs once inside each bucket.

Step 3: Why WHERE and not HAVING

Both filter, but they run at different times. WHERE runs before grouping, on individual rows. HAVING runs after, on the groups — so it can only see things that survived aggregation.

A row-level condition like status = 'completed' belongs in WHERE. A group-level one like "only cities with 3 or more completed rides" belongs in HAVING, because the count doesn't exist until the groups do. See Query execution order and HAVING.

Step 4: The two wrong answers

Dropping the filter. No error, four rows, and every single number is wrong:

SELECT city, COUNT(*) AS completed_rides FROM rides GROUP BY city;

Johor Bahru still reads 3, which is right by coincidence — all three of its rides completed. That coincidence is what lets this mistake survive a glance.

Moving the filter inside the aggregate. Postgres supports FILTER, and it counts correctly, but it changes which cities appear:

SELECT city, COUNT(*) FILTER (WHERE status = 'completed') AS completed_rides
FROM rides
GROUP BY city;

The three real counts match — but Ipoh is back with a zero, because grouping happened before the filter and Ipoh's cancelled ride was still there to make a group. That's the whole difference between the two clauses: WHERE decides which rows exist, FILTER only decides what gets counted. Neither is wrong in general; here the prompt asks for cities with at least one completed ride, so WHERE is the one that answers the question.