Count, sum, and group

One number per group

An aggregate collapses many rows into one number. GROUP BY runs that aggregate once per group. This is how you turn 1,200 orders into revenue by category, or a count of orders by store.

bruin query --connection duckdb-default \
  --description "order count and revenue by status" \
  --query "SELECT order_status, COUNT(*) AS orders, SUM(order_total) AS revenue FROM orders GROUP BY order_status ORDER BY orders DESC;"

Look at the result before moving on. One row's status prints as <nil> (some tools print NULL) - that is the group of orders with no status at all, 24 of them. NULL forms its own group in a GROUP BY. Keep an eye on that row; it is about to matter twice in this lesson.

WHERE filters rows before grouping. HAVING filters groups after aggregating. Use WHERE order_total > 0 to drop rows first; use HAVING SUM(order_total) > 10000 to keep only large groups. Mixing them up is the most common mistake at this level.

Three counts that disagree

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. Run all three against unit_cost, which is NULL on 57 order lines:

bruin query --connection duckdb-default \
  --description "three counts of unit_cost that disagree" \
  --query "SELECT COUNT(*) AS rows, COUNT(unit_cost) AS non_null, COUNT(DISTINCT unit_cost) AS distinct_values FROM order_items;"

rows is 2,880 and non_null is 2,823, a difference of exactly 57. AVG(unit_cost) divides by 2,823, not 2,880, because AVG skips NULLs. The denominator is not the one you assumed, so the average is higher than a per-line reading suggests.

An inclusion list drops NULLs too

You learned that != 'cancelled' drops NULL statuses. An inclusion list does the same thing, and it looks safer, which is why it catches people. order_status IN ('completed', 'shipped') keeps only those two values - and silently discards every NULL status along with the statuses you meant to exclude.

The statuses you can see it excluding - cancelled, returned, processing - are a choice you made on purpose. The NULL rows are not a choice. They appear in no list, so nothing about the query says they are gone. Measure exactly what vanished without your consent:

bruin query --connection duckdb-default \
  --description "orders silently dropped by an inclusion list" \
  --query "SELECT COUNT(*) AS dropped_orders, SUM(order_total) AS dropped_revenue FROM orders WHERE order_status IS NULL;"

The inclusion list silently drops 24 orders carrying 10,900.00 in order_total - the same 24 NULL-status rows the != filter dropped, lost the same way. An inclusion list is as dangerous as an exclusion list. Both eat NULLs.

Ask your coding agent

AI Prompt

Write me a query for the total revenue by product category for 2023, then explain your choices:

Which column did you use for revenue, and why that one instead of the alternatives?
Which date column did you filter on, and why?
Did you exclude any rows, and on what grounds?

Show the SQL before running it. If more than one reasonable definition of "revenue" exists in this data, list them and ask me which one I want rather than picking for me.

A well-directed agent asks before it guesses. If it picks a revenue column without telling you there was a choice, that is the behaviour to correct.

Checkpoint

Confirm the two numbers:

order_items has 2,880 rows and 2,823 non-NULL unit_cost values, a gap of 57.
order_status IN ('completed', 'shipped') silently drops the 24 NULL-status orders worth 10,900.00, on top of the statuses it visibly excludes.

Then answer for yourself, using the agent's query: which product category had the highest revenue in 2023?