Joins and Groups

Joins and GROUP BY go well together: the join puts the data from several tables into rows, and GROUP BY sums them up. How many copies of the games of each genre have customers ordered? The quantities are in order_items, the genres in games:

SELECT g.genre, SUM(i.quantity) AS copies
FROM order_items AS i
JOIN games AS g ON i.game_id = g.id
GROUP BY g.genre
ORDER BY copies DESC, g.genre;

The join gives a row for each item, with the genre of its game. GROUP BY then makes a group for each genre, and SUM adds up the quantities in each group.

Money works the same way. An item brought in its quantity times the price the customer paid for one copy: i.quantity * i.price. Not g.price: that's the price in the catalog, and in a sale, customers pay less. The money a group brought in, its revenue, is then SUM(i.quantity * i.price): SUM works out the expression for each row of the group and adds the results up.

When you group by something that has a name, such as a customer or a game, group by its id and add the name to GROUP BY too: GROUP BY c.id, c.name. Two customers can have the same name, and GROUP BY c.name alone would put them into one group, as if they were one person. Thanks to the name in GROUP BY, you can show it in SELECT: each group has one id, so it has one name as well. How many reviews has each customer written, and how many stars do they give on average?

SELECT c.name, COUNT(*) AS review_count, ROUND(AVG(r.stars), 1) AS average
FROM reviews AS r
JOIN customers AS c ON r.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY review_count DESC, c.name;

A refunded order brought in nothing, so revenue should count only the paid ones. The status is in orders: join it too, and keep only the paid orders with WHERE. WHERE runs before GROUP BY, so the rows of refunded orders are gone before anything is added up. HAVING then picks groups, as you know from the previous lesson.

Typical mistake: SUM(quantity * price) without the aliases. When games is in the query, price is ambiguous, and you get an error. SUM(i.quantity * g.price) is worse: it runs, but it adds up the prices from the catalog, and the sale disappears from the revenue.