Unit 02.04: Exploring safely without shipping a truncated total

Unit ID: SQL-M02-U05 - Estimated active time: 12-15 minutes Objective: use LIMIT while exploring without letting it corrupt a reported figure.

The habit that produces a wrong number

You add LIMIT 100 to keep exploration fast. Later you wrap the query in an aggregate and forget to remove it. The aggregate now describes 100 rows, not the population - and it looks completely normal.

-- exploring
SELECT order_id, order_total FROM orders WHERE status = 'completed' LIMIT 100;

-- the trap: this is the total of a sample, not of the population
SELECT SUM(order_total) FROM (
  SELECT order_total FROM orders WHERE status = 'completed' LIMIT 100
) t;

The second query returns a real number, formatted correctly, that answers a question nobody asked.

The correct total

SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders
WHERE status = 'completed';
-- 988 | 2685905.00

Report the count alongside the sum. 988 immediately tells a reader the population, and it is the cheapest possible defence against a silently truncated aggregate.

A safer exploration pattern

Explore with LIMIT, but aggregate without it and check the count matches your expectation:

-- 1. Look at the shape
SELECT * FROM orders WHERE status = 'completed' LIMIT 5;

-- 2. Confirm the population before summarising
SELECT COUNT(*) FROM orders WHERE status = 'completed';   -- 988

-- 3. Then aggregate, and confirm the count again in the same result
SELECT COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders WHERE status = 'completed';

Step 3 carrying COUNT(*) is what makes the number self-checking.

Practice

Someone sends you this and asks you to publish the figure. What do you check first?

SELECT AVG(order_total) FROM orders LIMIT 500;

Check your answer

Two things are wrong, and the second is subtler than the first.

There is no COUNT(*), so you cannot see the population the average describes.
LIMIT 500 applies to the result, not the input. Because this query has no GROUP BY, the

aggregate produces a single row, so the LIMIT does nothing whatsoever - it does not restrict the average to 500 orders. The figure returned is 2701.463, the average over all 1,000 orders, including the 12 pending ones that probably should have been excluded.

Anyone who reads LIMIT 500 and assumes the average covers 500 orders has been misled by a clause that had no effect. Worth trying: add ORDER BY placed_at to that query and DuckDB rejects it outright, because placed_at is neither grouped nor aggregated - the engine will not let you order a one-row aggregate by a column it never grouped on.

Correct version, stating both the population and the status filter:

SELECT COUNT(*) AS orders, ROUND(AVG(order_total), 2) AS avg_order_value
FROM orders
WHERE status = 'completed';
-- 988 | 2718.53

Takeaway

Never let LIMIT reach an aggregate you intend to report. Carry COUNT(*) beside every total so the population is visible in the result itself.

---