The sales tile says two customers ordered, but it was only Hugo

See the problem

The admin home page has a sales tile for each day: how many orders came in, from how many customers, what they were worth, and the average order. Behind it is this query, here for 20 October:

The tile’s query

SELECT
  count(*) AS orders,
  count(customer_id) AS customers,
  sum(total) AS sales,
  round(avg(total), 2) AS avg_order
FROM orders
WHERE order_date = '2026-10-20';

On 20 October it shows 1 order from 1 customer, worth 142.99. Then Hugo orders again the same afternoon, a 20.00 order. The tile now says:

orders	customers	sales	avg_order
2	2	162.99	81.50

Two customers? It was only Hugo, twice. And on a day with no orders, the tile says 0 orders and 0 customers, but its sales figure is blank. The page that formats it as money breaks.

Define the task

Write the tile’s query for 20 October: one row with these four columns.

• orders: how many orders came in that day.
• customers: how many different customers placed them.
• sales: their totals added up, and 0 on a day with no orders.
• avg_order: the average total, rounded to 2 places. On a day with no orders there’s no average, so NULL is right.

Pause and think

⏸ Pause and think

count(customer_id) counted Hugo twice. What does it count: customers, or rows? And what is the sum of no numbers at all?

Give it two minutes with pen and paper before you open a hint.

Hints open one at a time:

Hint 1 of 3

count(customer_id) counts the rows where customer_id isn’t NULL. Each of Hugo’s orders is a row.

Hint 2 of 3

count(DISTINCT customer_id) counts different customers, so Hugo counts once.

Hint 3 of 3

sum() of no rows is NULL, not 0. coalesce(sum(total), 0) gives 0 when the sum is NULL.

Understand the idea

An aggregate folds many rows into one value. count, sum, avg, min and max each read every row that’s left after WHERE and give one value. With no GROUP BY, the result is always one row, even when no rows are left.

Runs	Clause	What it does
1	FROM orders	Reads the table’s rows
2	WHERE order_date = '2026-10-20'	Keeps one day’s orders
3	count(*), sum(total), …	Fold the rows that are left into one row
4	ORDER BY, LIMIT	Sort and cut, if there’s more than one row

WHERE runs before the aggregates, so it can’t use them: WHERE avg(total) > 50 is an error. The next lesson filters on an aggregate with HAVING.

Three ways to count.

• count(*) counts rows.
• count(customer_id) counts rows where customer_id isn’t NULL.
• count(DISTINCT customer_id) counts different values, and skips NULL too.

The store’s events table has 20 events: 19 from signed-in customers, and they are 7 different customers. So it gives 20, 19 and 7.

sum, avg, min and max skip NULLs. The average of 10, NULL and 20 is 15, not 10: the NULL isn’t counted as 0, it’s left out. If a NULL should count as 0, say so: avg(coalesce(x, 0)).

No rows: count gives 0, the others give NULL. There’s nothing to add, so sum() doesn’t pretend the answer is 0. coalesce(sum(total), 0) gives the first value that isn’t NULL, so a day with no orders shows 0. Leave avg() as NULL: a day with no orders has no average order.

Watch the tile fold 20 October’s orders into one row, one aggregate at a time. Then type your own orders, with one customer twice, or none on the 20th:

The sales tile, step by step

⤢ Full view

1. FROM orders
2. WHERE order_date = '2026-10-20'
3. count(*)
4. count(DISTINCT customer_id)
5. coalesce(sum(total), 0)
6. round(avg(total), 2)

orders

customer_id	order_date	total
7	2026-10-16	9.99
6	2026-10-18	49.98
8	2026-10-20	142.99
8	2026-10-20	20.00

Step 1 of 7. FROM orders reads all 4 rows.

◀ BackPlayNext ▶Reset Ask me first Show as text

1SELECT
2  count(*) AS orders,
3  count(DISTINCT customer_id) AS customers,
4  coalesce(sum(total), 0) AS sales,
5  round(avg(total), 2) AS avg_order
6FROM orders
7WHERE order_date = '2026-10-20';

Try your own input

Orders: customer_id, order_date, total7, 2026-10-16, 9.99 6, 2026-10-18, 49.98 8, 2026-10-20, 142.99 8, 2026-10-20, 20.00Show it

Watch the types in a division. count() gives a whole number, and whole number divided by whole number stays whole: 7 / 2 is 3. Write 100.0 * count(customer_id) / count(*) for a percentage with decimals. And x / 0 is an error, so nullif(count(*), 0) turns a 0 into NULL, and the answer is NULL instead. Practice problem 2 meets both.

FILTER counts some of the rows. count(*) FILTER (WHERE status = 'delivered') counts only delivered orders, next to a plain count(*) in the same row. Practice problem 3 uses it.

An aggregate and a plain column don’t mix. SELECT customer_id, count(*) FROM orders is an error: there’s one count, but a customer_id on each of the 14 orders. To get a count for each customer, group the rows, in the next lesson.

Climb the ladder

All three count the day’s orders and add up their totals. What changes is what counts as a customer, and what a day with no orders shows.

Okay

count(customer_id)

The idea: Count the orders, count their customers, add up the totals.

It looks right because on the store’s 20 October only Hugo ordered, once.

SELECT
  count(*) AS orders,
  count(customer_id) AS customers,
  sum(total) AS sales,
  round(avg(total), 2) AS avg_order
FROM orders
WHERE order_date = '2026-10-20';

Gets right

Days when nobody orders twice, like the store’s 20 October

Gets wrong

A customer who orders twice

Why

count(customer_id) counts rows that have a customer_id, so Hugo counts once for each order.

Where it breaks: Hugo orders again, and the tile says 2 customers. Okay passes 1 of the 3 checks below.

Better

count(DISTINCT customer_id)

The idea: Count each customer once.

The gap in Okay: it counted orders, not customers.

Highlighted lines are new in this rung.

SELECT
  count(*) AS orders,
  count(DISTINCT customer_id) AS customers,
  sum(total) AS sales,
  round(avg(total), 2) AS avg_order
FROM orders
WHERE order_date = '2026-10-20';

Gets right

Customers who order more than oncewas Days when nobody orders twice, like the store’s 20 October

Gets wrong

A day with no orders: sales is NULL, not 0

Why

sum() of no rows is NULL, so the tile shows a blank.

What changed from the previous rung

SELECT
  count(*) AS orders,
  count(customer_id) AS customers,
  count(DISTINCT customer_id) AS customers,
  sum(total) AS sales,
  round(avg(total), 2) AS avg_order
FROM orders
WHERE order_date = '2026-10-20';

Where it breaks: Hugo’s order moves to 21 October, so 20 October has none. The tile gets 0 orders, 0 customers and NULL sales. Better passes 2 of 3.

Best

coalesce(sum(total), 0)

The idea: Turn the NULL sum of no orders into 0.

The gap in Better: no orders meant no sum.

Highlighted lines are new in this rung.

SELECT
  count(*) AS orders,
  count(DISTINCT customer_id) AS customers,
  coalesce(sum(total), 0) AS sales,
  round(avg(total), 2) AS avg_order
FROM orders
WHERE order_date = '2026-10-20';

Gets right

Every check, a day with no orders toowas Customers who order more than once

Gets wrong

Nothing the checks try

Why

coalesce() gives the first value that isn’t NULL.

What changed from the previous rung

SELECT
  count(*) AS orders,
  count(DISTINCT customer_id) AS customers,
  sum(total) AS sales,
  coalesce(sum(total), 0) AS sales,
  round(avg(total), 2) AS avg_order
FROM orders
WHERE order_date = '2026-10-20';

When Best isn’t best: to show a tile for every day at once, you group the orders by day, in the next lesson. Then a day with no orders has no row at all, and you need a list of days to start from. Also ask whether a cancelled order should count: if not, add AND status <> 'cancelled'.

Write it yourself

Now write it in the page. Press Run query to see your result and check it. Postgres runs in your browser, so nothing is sent anywhere. Each check changes a few rows first, then they’re put back.

Try Okay from the ladder first, and open a failed check to see what it changed.

The sales tile

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)

-- 20 October: orders, customers, sales (0 when none), avg_order. SELECT order_id, customer_id, total FROM orders WHERE order_date = '2026-10-20';

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data
2. ○Not run yet: Hugo orders again the same day
   What this check changes

   INSERT INTO orders (order_id, customer_id, order_date, status, total)
   VALUES (115, 8, '2026-10-20', 'placed', 20.00);

3. ○Not run yet: Hugo’s order moves to the next day, so 20 October has none
   What this check changes

   UPDATE orders SET order_date = '2026-10-21' WHERE order_id = 114;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Compare

Way	Gets right	Gets wrong	Use it when
Okay: count(customer_id)	Orders, and customers who order once	A customer who orders twice	You want rows with a value, not different values
Better: count(DISTINCT customer_id)	Different customers	A day with no orders: NULL	The caller turns NULL into 0
Best: coalesce(sum(total), 0)	Every day, quiet ones too	Nothing the checks try	A tile or report that shows a number

Common mistakes and edge cases

!sum() of no rows is NULL, not 0

Only count() gives 0 on no rows. sum, avg, min and max give NULL. Wrap sum() in coalesce(..., 0) when the reader expects a number, and leave avg() alone: no rows means no average.

• count(column) skips NULLs. It counts rows that have a value, not rows. Use count(*) to count rows.
• Whole numbers divide to a whole number. count(a) / count(b) drops the decimals, so 1 / 3 is 0. Multiply by 100.0 first.
• Division by zero is an error. Divide by nullif(count(*), 0) to get NULL instead.
• count(status = 'delivered') counts every row. The comparison is true or false, and count counts both. Use count(*) FILTER (WHERE status = 'delivered').
• No aggregates in WHERE. WHERE price = min(price) is an error: “aggregate functions are not allowed in WHERE”. Work it out in a subquery: WHERE price = (SELECT min(price) FROM products). Practice problem 1 does this.
• avg() of numbers gives many decimals. avg(total) of 142.99 and 20.00 is 81.4950000000000000. Round it with round(avg(total), 2).

Say it in the interview

Questions to ask first

• Customers or orders: should someone who orders twice count once?
• On a day with no orders, should the value be 0, or blank?
• Do cancelled orders count?
• Which time zone does a day start in?

A 60-second walkthrough

“One row, so no GROUP BY: I filter to the day in WHERE. Then I use count(*) for orders and count(DISTINCT customer_id) for customers. count(customer_id) counts rows, so it would count a repeat customer twice. For the value I use coalesce(sum(total), 0), since sum() of no rows is NULL, not 0. I leave the average as round(avg(total), 2). On a day with no orders it’s NULL, because there’s no average to show. I’d check whether cancelled orders should count.”

Likely follow-ups

• What’s the difference between count(*) and count(1)? None you’d notice: both count rows. count(column) is the one that differs, because it skips NULLs.
• How do you find products at the lowest price? WHERE price = (SELECT min(price) FROM products): an aggregate can’t go in WHERE, but a subquery that uses one can.
• How would you show this tile for each day? GROUP BY order_date, in the next lesson.

Practice

Three problems, from easy to medium. Each has hints, checks you can run here, and its own Okay, Better and Best answers. Try before you open them.

1. The cheapest products (easy)

The “lowest price” badge goes on every product at the store’s lowest price. List them: product_id, name and price, by product_id.

Hint 1 of 3

min(price) gives the lowest price. Which products have it?

Hint 2 of 3

ORDER BY price LIMIT 1 keeps one product, even when two share the lowest price.

Hint 3 of 3

WHERE price = (SELECT min(price) FROM products), then ORDER BY product_id, so the order doesn’t depend on how the rows are stored.

The cheapest products

Ctrl + Enter runs

The tables

• products (product_id, name, category, price)

SELECT product_id, name, price FROM products;

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data (row order matters)
2. ○Not run yet: An eraser joins stationery at 3.20, the gel pens’ price (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Eraser', 'stationery', 3.20);

3. ○Not run yet: The eraser joins at 3.20, then the gel pens get a new name (row order matters)
   What this check changes

   INSERT INTO products (product_id, name, category, price)
   VALUES (9, 'Eraser', 'stationery', 3.20);
   UPDATE products SET name = 'Gel pens (5 pack)' WHERE product_id = 2;

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

ORDER BY price LIMIT 1

The idea: Sort by price and keep the first.

Okay passes 1 of 3. When an eraser joins at 3.20, the gel pens’ price, only one of them gets the badge.

SELECT product_id, name, price
FROM products
ORDER BY price
LIMIT 1;

Gets right

One product at the lowest price, as in the store

Gets wrong

Two products at the lowest price

Why

LIMIT 1 keeps one row, whatever ties with it.

Better

WHERE price = (SELECT min(price) ...)

The idea: Work out the lowest price in a subquery, then keep every product at it.

The gap in Okay: LIMIT 1 cut a tie.

Highlighted lines are new in this rung.

SELECT product_id, name, price
FROM products
WHERE price = (SELECT min(price) FROM products);

Gets right

Every product at the lowest price

Gets wrong

Their order

Why

With no ORDER BY, the rows come in the order they’re stored.

What changed from the previous rung

SELECT product_id, name, price
FROM products
ORDER BY price
LIMIT 1;
WHERE price = (SELECT min(price) FROM products);

Where it breaks: the gel pens get a new name, so their row is stored again at the end, and the eraser now comes first. Better passes 2 of 3.

Best

Then ORDER BY product_id

The idea: Say the order.

The gap in Better: no ORDER BY.

Highlighted lines are new in this rung.

SELECT product_id, name, price
FROM products
WHERE price = (SELECT min(price) FROM products)
ORDER BY product_id;

Gets right

Every check

Gets wrong

Nothing the checks try

Why

product_id is unique, so every row has one place.

What changed from the previous rung

SELECT product_id, name, price
FROM products
WHERE price = (SELECT min(price) FROM products);
WHERE price = (SELECT min(price) FROM products)
ORDER BY product_id;

When Best isn’t best: for the cheapest product in each category, the subquery has to be worked out per category. Window functions do that, as in each customer’s latest order.

2. Signed-in share of page views (medium)

What percentage of 16 October’s page views came from signed-in customers? Visitors who aren’t signed in have no customer_id. Show signed_in_pct with 1 decimal place, or NULL when there were no page views.

Hint 1 of 3

count(customer_id) counts signed-in views, and count(*) counts all of them.

Hint 2 of 3

Both counts are whole numbers, so 100 * 1 / 3 is 33, not 33.3. Use 100.0.

Hint 3 of 3

With no page views, count(*) is 0, and dividing by 0 is an error. Divide by nullif(count(*), 0).

Signed-in share of page views

Ctrl + Enter runs

The tables

• events (event_id, customer_id, event_type, occurred_at)

SELECT customer_id, occurred_at FROM events WHERE event_type = 'page_view' AND occurred_at >= '2026-10-16' AND occurred_at < '2026-10-17';

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data
2. ○Not run yet: Another visitor views a page without signing in
   What this check changes

   INSERT INTO events (event_id, customer_id, event_type, occurred_at)
   VALUES (21, NULL, 'page_view', '2026-10-16 12:00:00');

3. ○Not run yet: No page views on 16 October
   What this check changes

   DELETE FROM events WHERE event_type = 'page_view' AND occurred_at::date = '2026-10-16';

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

100 * count(customer_id) / count(*)

The idea: Signed-in views over all views, times 100.

Okay passes 1 of 3. One more visitor makes it 1 of 3 views: the answer is 33, not 33.3. With no views at all, it stops with “division by zero”.

SELECT 100 * count(customer_id) / count(*) AS signed_in_pct
FROM events
WHERE event_type = 'page_view'
  AND occurred_at >= '2026-10-16'
  AND occurred_at < '2026-10-17';

Gets right

Shares that come out whole, like the store’s 50

Gets wrong

Any share with decimals; a day with no views

Why

Whole numbers divide to a whole number, so the decimals are dropped.

Better

100.0, and round

The idea: Make it a decimal division, then round to 1 place.

The gap in Okay: whole-number division.

Highlighted lines are new in this rung.

SELECT round(100.0 * count(customer_id) / count(*), 1) AS signed_in_pct
FROM events
WHERE event_type = 'page_view'
  AND occurred_at >= '2026-10-16'
  AND occurred_at < '2026-10-17';

Gets right

Shares with decimals

Gets wrong

A day with no page views

Why

count(*) is 0, and dividing by 0 is an error.

What changed from the previous rung

SELECT 100 * count(customer_id) / count(*) AS signed_in_pct
SELECT round(100.0 * count(customer_id) / count(*), 1) AS signed_in_pct
FROM events
WHERE event_type = 'page_view'
  AND occurred_at >= '2026-10-16'
  AND occurred_at < '2026-10-17';

Where it breaks: on a day with no page views, it stops with “division by zero”, and the report fails. Better passes 2 of 3.

Best

Divide by nullif(count(*), 0)

The idea: Turn a count of 0 into NULL, so the share is NULL.

The gap in Better: a division by 0.

Highlighted lines are new in this rung.

SELECT round(100.0 * count(customer_id) / nullif(count(*), 0), 1) AS signed_in_pct
FROM events
WHERE event_type = 'page_view'
  AND occurred_at >= '2026-10-16'
  AND occurred_at < '2026-10-17';

Gets right

Every check

Gets wrong

Nothing the checks try

Why

nullif(a, b) gives NULL when a equals b, and dividing by NULL gives NULL.

What changed from the previous rung

SELECT round(100.0 * count(customer_id) / count(*), 1) AS signed_in_pct
SELECT round(100.0 * count(customer_id) / nullif(count(*), 0), 1) AS signed_in_pct
FROM events
WHERE event_type = 'page_view'
  AND occurred_at >= '2026-10-16'
  AND occurred_at < '2026-10-17';

When Best isn’t best: the date range uses >= the start and < the next day, so it works for times. If the app runs in another time zone, the day starts at a different moment: store times as timestamptz and say which zone a day is in.

3. September in one row (medium)

The monthly report shows September 2026 in one row: orders (all of them), delivered (how many were delivered) and delivered_value (the delivered ones’ totals, 0 when none were).

Hint 1 of 3

Three numbers about different rows, but one row: one WHERE for the month, and each aggregate picks its own rows.

Hint 2 of 3

count(status = 'delivered') counts true and false alike. count(*) FILTER (WHERE status = 'delivered') counts only the delivered ones.

Hint 3 of 3

sum() with a FILTER that keeps no rows is NULL. Wrap it in coalesce(..., 0).

September in one row

Ctrl + Enter runs

The tables

• orders (order_id, customer_id, order_date, status, total)

SELECT order_id, status, total FROM orders WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01';

Run queryShow the stepsReset queryLoads about 5 MB the first time. Best on Wi-Fi.

1. ○Not run yet: The store’s data
2. ○Not run yet: Order 105 is cancelled
   What this check changes

   UPDATE orders SET status = 'cancelled' WHERE order_id = 105;

3. ○Not run yet: No September order has been delivered yet
   What this check changes

   UPDATE orders SET status = 'shipped' WHERE order_date < '2026-10-01';

Each check runs on its own copy of the data, so your query can’t change the real tables.

Show the Okay, Better and Best answers

Okay

count(status = 'delivered')

The idea: Count the rows where status is delivered, and add up the totals.

Okay passes 1 of 3. When order 105 is cancelled, it still says 5 delivered, worth 219.56.

SELECT
  count(*) AS orders,
  count(status = 'delivered') AS delivered,
  sum(total) AS delivered_value
FROM orders
WHERE order_date >= '2026-09-01'
  AND order_date < '2026-10-01';

Gets right

The store’s September, when every order was delivered

Gets wrong

Any order that wasn’t delivered

Why

The comparison is true or false, and count() counts both; sum(total) adds every order.

Better

FILTER (WHERE status = 'delivered')

The idea: Let each aggregate pick its own rows.

The gap in Okay: it counted every order.

Highlighted lines are new in this rung.

SELECT
  count(*) AS orders,
  count(*) FILTER (WHERE status = 'delivered') AS delivered,
  sum(total) FILTER (WHERE status = 'delivered') AS delivered_value
FROM orders
WHERE order_date >= '2026-09-01'
  AND order_date < '2026-10-01';

Gets right

Orders that weren’t delivered

Gets wrong

A month with no deliveries: NULL

Why

sum() over no rows is NULL, with a FILTER too.

What changed from the previous rung

SELECT
  count(*) AS orders,
  count(status = 'delivered') AS delivered,
  sum(total) AS delivered_value
  count(*) FILTER (WHERE status = 'delivered') AS delivered,
  sum(total) FILTER (WHERE status = 'delivered') AS delivered_value
FROM orders
WHERE order_date >= '2026-09-01'
  AND order_date < '2026-10-01';

Where it breaks: if no September order has been delivered yet, delivered_value is NULL. Better passes 2 of 3.

Best

coalesce(sum(...) FILTER (...), 0)

The idea: A month with no deliveries shows 0.

The gap in Better: no deliveries meant no sum.

Highlighted lines are new in this rung.

SELECT
  count(*) AS orders,
  count(*) FILTER (WHERE status = 'delivered') AS delivered,
  coalesce(sum(total) FILTER (WHERE status = 'delivered'), 0) AS delivered_value
FROM orders
WHERE order_date >= '2026-09-01'
  AND order_date < '2026-10-01';

Gets right

Every check

Gets wrong

Nothing the checks try

Why

coalesce turns the NULL sum into 0.

What changed from the previous rung

SELECT
  count(*) AS orders,
  count(*) FILTER (WHERE status = 'delivered') AS delivered,
  sum(total) FILTER (WHERE status = 'delivered') AS delivered_value
  coalesce(sum(total) FILTER (WHERE status = 'delivered'), 0) AS delivered_value
FROM orders
WHERE order_date >= '2026-09-01'
  AND order_date < '2026-10-01';

When Best isn’t best: FILTER is standard SQL, but not every database has it. Elsewhere, count(CASE WHEN status = 'delivered' THEN 1 END) does the same.

Check yourself

A few quick questions. Ones you miss are remembered, so you can find your weak spots later.

1. Predict the outputOn 20 October, Hugo (customer 8) placed two orders, and nobody else ordered. What does this query return?

   SELECT count(*), count(customer_id), count(DISTINCT customer_id)
   FROM orders
   WHERE order_date = '2026-10-20';

   2, 1, 12, 2, 12, 2, 21, 1, 1

   Check answer
2. Multiple choiceNo order was placed on 1 January. What does SELECT sum(total) FROM orders WHERE order_date = '2026-01-01' give?

   No rowsOne row: 0An errorOne row: NULL

   Check answer
3. Predict the output8 of the store’s 9 page views are from signed-in customers. What does this query give?

   SELECT 100 * count(customer_id) / count(*) AS signed_in_pct
   FROM events
   WHERE event_type = 'page_view';

   88.98889An error

   Check answer
4. Multiple choiceA column x holds 10, NULL and 20. What does avg(x) give?

   1510NULL30

   Check answer
5. Spot the bugThis should list the products at the lowest price. What happens?

   SELECT product_id, name
   FROM products
   WHERE price = min(price);

   Every product at the lowest priceOne product at the lowest pricePostgres stops with an errorNo rows

   Check answer

Flashcards

Add these to your review deck. Each card comes back a little later every time you get it right.

• Card 1What do count(*), count(col) and count(DISTINCT col) count?Show answer
• Card 2What do count, sum, avg, min and max give on no rows?Show answer
• Card 3How do sum and avg treat NULLs?Show answer
• Card 4Why is 7 / 2 equal to 3 in Postgres, and how do you get 3.5?Show answer
• Card 5How do you avoid "division by zero" in a / b?Show answer
• Card 6How do you count only some rows, next to a count of all of them?Show answer

Add 6 cards to my review deck

Cheat sheet

• count(*) counts rows; count(col) skips NULLs; count(DISTINCT col) counts different values.
• sum, avg, min, max skip NULLs, and give NULL on no rows. count gives 0.
• coalesce(sum(x), 0) for a sum that shows 0. Leave avg as NULL when there are no rows.
• No aggregates in WHERE: use a subquery, or HAVING (next lesson).
• Percentages: 100.0 * a / nullif(b, 0), then round(..., 1).
• count(*) FILTER (WHERE ...) counts some rows in the same row as the rest.

Go deeper

• In the PostgreSQL docs: aggregate functions, aggregate expressions and FILTER and COALESCE and NULLIF.
• Try your own orders: the sales tile on the SQL visualizer.
• Next on the SQL roadmap: the whole map, from your first SELECT to window functions.

Can you do these now?

If you’re not sure of any of these, scroll back to that part before moving on.

• Use count, sum, avg, min and max, and say which rows each one counts
• Tell count(*), count(column) and count(DISTINCT column) apart
• Say what each aggregate gives on no rows, and show 0 with coalesce
• Avoid integer division and division by zero in a percentage, and count some rows with FILTER

✓Mark as completeI already know this

Status: Not started