Sums and Averages

COUNT counts; SUM adds up. SUM(column) adds up the values of a column in all the rows found, and AVG(column) gives their average: the sum divided by how many values there are. If you bought one copy of every game in the shop, you'd pay:

SELECT SUM(price) AS all_games,
       AVG(price) AS average_price
FROM games;

Remember from lesson 4 that 7 / 2 is 3, because both numbers are whole? AVG doesn't do that: it always gives a decimal number, even when all the values are whole. And an average often has many decimal places, so you'll usually round it with ROUND. This is the average price in order_items, as it is and rounded:

SELECT AVG(price) AS average_paid,
       ROUND(AVG(price), 2) AS rounded
FROM order_items;

In the parentheses, you can write any expression, not just a column. The database works it out for each row and then adds up the results. That's how you work out what orders are worth. In order_items, each row has the number of copies and the price of one copy, so a row is worth quantity * price. These are the items of the orders 5 and 6:

SELECT order_id, game_id, quantity, price,
       quantity * price AS item_total
FROM order_items
WHERE order_id IN (5, 6);

And this adds up their item_total:

SELECT SUM(quantity * price) AS total
FROM order_items
WHERE order_id IN (5, 6);

A sum over the whole table order_items is the value of everything that customers have ordered. That's not quite what the shop has earned: it includes the items of the two refunded orders, for which the customers got their money back. Whether an order was refunded is written in another table, orders, and you'll learn to combine tables in the next lesson. Until then, the sums in this lesson include the refunded orders, except in the last exercise, where you'll leave them out another way.

Like COUNT(column), SUM and AVG skip NULL, so AVG divides by the number of values, not of rows. And when there's nothing to add up, because WHERE found no row, they give NULL, not 0. If you need a 0 there, use COALESCE(SUM(price), 0) from lesson 4.

Typical mistake: SUM(price) instead of SUM(quantity * price). In order 6, the customer bought two copies of Pixel Racer, and SUM(price) would count only one of them.