Aggregates: Crunch the Numbers

One number from many rows

An aggregate function takes a whole column of values and returns a single value. The big five: COUNT, SUM, AVG, MIN, MAX.

Example: How many pizzas do we sell?

SELECT COUNT(*) AS pizza_count FROM pizzas;

COUNT(*) counts rows. COUNT(column) counts rows where that column is NOT NULL.

SUM and AVG

Add them up or average them. Combine with WHERE to aggregate over a subset.

Example: Average price of a vegetarian pizza

SELECT AVG(price) AS avg_veg_price FROM pizzas WHERE is_vegetarian = 1;

WHERE filters rows first; then AVG runs over what is left.

MIN, MAX, and ROUND

Several aggregates can live in one SELECT. ROUND(x, 2) tidies up long decimals.

Example: Price range and a tidy average

SELECT MIN(price) AS cheapest, MAX(price) AS priciest, ROUND(AVG(price), 2) AS average FROM pizzas;

Three aggregates, one row.

Aggregates over expressions

The thing inside the parentheses can be any expression. SUM(quantity * price) is how revenue gets computed everywhere in the world.

Example: Total pizzas ever ordered

SELECT SUM(quantity) AS total_pizzas FROM orders;

There are 20 orders, but some are for 2, 3 or 4 pizzas. The sum tells the real story.

COUNT DISTINCT

Want to know how many *different* values there are? COUNT(DISTINCT column).

Example: How many customers have actually ordered?

SELECT COUNT(DISTINCT customer_id) AS active_customers FROM orders;

Not every customer has placed an order (looking at you, Felix).

Practice exercises

1. Head count

   Count how many customers there are. Name the column total_customers.

2. Payroll

   What is the highest hourly_wage among chefs? Name the column top_wage.

3. Deep Dish calories

   Find the average calories of pizzas in the 'Deep Dish' category. Name it avg_calories.

4. Loyalty in Austin

   Sum the loyalty_points of all customers from 'Austin'. Name it austin_points.

5. Distinct pizzas ordered

   How many different pizzas have been ordered at least once? Count distinct pizza_id in orders, name it pizzas_ordered.

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the Aggregates quiz: 5 timed questions.