Skip to the content
Start

SUM and AVG

SQL · Unit 5: Aggregates

Twelve orders don't say how much was ordered: one was for 10 Cables and another for a single Laptop. How many units have been ordered from the store? SUM goes through a column of numbers and adds it up from top to bottom.

The money that came in can't be worked out this way yet. orders stores how many units each order was for, and the price of each one lives in another table.

SELECT SUM(quantity) AS units
FROM orders;

Returns

units
30

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    The store has 12 orders so far, and together they add up to 30 units. What does this query return?

  2. 2. Complete the code

    It's inventory day. How many units are in the warehouse, across all the products? Fill in the blank.

  3. 3. Find the bug

    Someone works out the average units per order by hand, with the sum over the count. That's 30 over 12, which gives 2.5. The query says 2. Which line has the problem?

  4. 4. Put the lines in order

    How much does a product in the store cost, on average? Put in order the query that gives that average with two decimals. Two lines don't belong.

  5. 5. Find the bug

    What's in the warehouse is worth 7923.5. This query runs without an error and says more than a hundred thousand. Which line has the problem?

  6. 6. Complete the code

    Each shelf holds 30 units of a product. How many units would the truck have to bring to fill the eight shelves? Fill in the blank.

  7. 7. Predict the output

    The seven ages on file add up to 238. Divided by 7 that's 34, and divided by 8 it's 29.75. What does this query return?

  8. 8. Multiple choice

    Last unit, 27 + NULL gave NULL. The age column has one NULL, Hannah's. Why does SUM(age) give 238 and not NULL?

  9. 9. Put the lines in order

    The owner asks for the customers' average age, and nobody makes up an age for Hannah. Put in order the query that gives three numbers, in this order: how many customers there are, how many have an age on file, and the average age. One line doesn't belong.

  10. 10. Write the code

    The average price, 201.57, treats the Laptop, with 3 in stock, the same as the Mouse, with 30. How much is one unit in the warehouse worth, on average? Write a query with a single column: the total value of the warehouse, which is the sum of price * stock, divided by the total number of units, which is the sum of stock, and rounded to two decimals.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →