The Most Wished Games

The wishlist works, and the shop's first question is: which games do customers want most? The answer helps it choose the games for the next sale, and it shows how much interest there is in Robot Rebellion and Mystery Manor before they come out.

It's a question for GROUP BY: one group for each game, and COUNT(*) counts the rows in it, that is, how many customers have the game on their wishlist. The wishlist has only IDs, so join it with games to get the titles, and group by the game, GROUP BY g.id, g.title.

Several games will have the same number of wishes. To get one exact order, sort by the number first and then by the title. In ORDER BY, you can use the alias of a column, such as ORDER BY wishes DESC.

A count that leaves out the games nobody wishes for tells only half of the story. To keep those games too, start from games and use LEFT JOIN: a game without wishes then gets one row, with NULL in the columns of the wishlist.

Typical mistake: COUNT(*) with LEFT JOIN. The game without wishes still has that one row, so COUNT(*) gives 1. Count a column of the joined table instead: COUNT(column) skips NULL, so it gives 0. Here's the difference on the number of orders of each customer:

SELECT c.name, COUNT(*) AS wrong, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count, c.name
LIMIT 3;

Henry Scott and Max Baker have never ordered, but COUNT(*) gives them 1 order each.