SQL Basics · Lesson 6 of 9

Grouping

An aggregate function turns the whole table into one row. GROUP BY splits the table into groups first, and gives you one row per group:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category;

Read it as: put the products with the same category together, then count each pile. The result has one row for each category.

The rule to remember: every column in the SELECT list must either be in the GROUP BY, or be inside an aggregate function. Asking for name here would make no sense, since each group contains many names.

Several values per group

SELECT category,
       COUNT(*) AS products,
       ROUND(AVG(price), 2) AS average_price,
       SUM(stock) AS items_in_stock
FROM products
GROUP BY category
ORDER BY average_price DESC;

You can sort by an alias, as in the last line.

HAVING: filtering groups

WHERE filters rows before they are grouped. To filter the groups themselves, by a count or a total, use HAVING after the GROUP BY:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
HAVING COUNT(*) >= 3;

That keeps only the categories with at least three products.

Tables in the practice database
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, customer_id, product_id, quantity, ordered

Every run starts from a fresh copy of the database, so nothing you do can break it.

Your Task

  1. Count how many customers live in each city.
  2. Return two columns: city and the count, named customers.
  3. Sort the result so the city with the most customers comes first. Sort cities with the same count alphabetically.
  • The query uses GROUP BY
  • The result has the columns city and customers
  • Each city appears once, with the right count
  • The cities with the most customers come first, and ties are in alphabetical order