Skip to the content
Start

What can go in the SELECT

SQL · Unit 6: Grouping

Last unit you summarized a column with four aggregates in a single row. What's in each section of the store: how many products, and in what price range? It's the same summary, done once per group.

The SELECT takes the group's column and, separated by commas, whichever aggregates you want. Each one goes through its group on its own.

SELECT category,
  COUNT(*) AS how_many,
  MIN(price) AS lowest,
  MAX(price) AS highest
FROM products
GROUP BY category;

Returns

categoryhow_manylowesthighest
Accessories35.2545.5
Audio259.974
Devices2320899
Displays1189189

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. Complete the code

    For each product someone has ordered: how many orders it has, and how many units they add up to. Fill in the third column.

  2. 2. Predict the output

    Accessories has 12 Keyboards, 30 Mice, and no Cables. Audio has 15 Headphones and no Speakers. Devices has 3 Laptops and 7 Tablets, and Displays has 5 Monitors. What does this query return?

  3. 3. Predict the output

    Luke, who is 27, and Hannah, with no age on file, live in Austin. Ann, Valerie, and Brian, who are 34, 52, and 38, live in Denver. The other groups have one person each. What does this query return?

  4. 4. Find the bug

    The owner asked how many units each section has in the warehouse. Someone added the name "to make it clearer," and here the report says Accessories, Keyboard, 42. There are 12 Keyboards. Which line has the problem?

  5. 5. Multiple choice

    Even so, the owner wants a sample product next to each section. What can go in the SELECT of a query with GROUP BY category without any engine rejecting it?

  6. 6. Put the lines in order

    A summary by city that doesn't hide gaps: how many customers there are and how many have an age on file, in that order. Put the query in order. Two lines don't belong.

  7. 7. Complete the code

    The sections, from the one with the most products to the one with the fewest. Audio and Devices tie, and the tie is broken alphabetically. Fill in the blank.

  8. 8. Predict the output

    The most expensive product in each section costs 899 in Devices, 189 in Displays, 74 in Audio, and 45.5 in Accessories. This query's SELECT doesn't ask for any price. What does it return?

  9. 9. Put the lines in order

    Which customer has ordered the most units, across all their orders? Put in order the query that returns only that customer, with the total. One line doesn't belong.

  10. 10. Write the code

    The ranking of what gets ordered most. Write a query with three columns, in this order: the product_id, how many orders it has, and how many units they add up to. The product with the most units goes first, with ties broken by product_id, lowest to highest.

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 →