Level 2 · Total units and revenue

SUM, AVG, MIN and MAX work like COUNT: they collapse rows into one value. They take a column or any expression:

SELECT
  SUM(quantity) AS total_units,
  AVG(unit_price) AS avg_line_price,
  MIN(unit_price) AS cheapest_line,
  MAX(unit_price) AS priciest_line
FROM order_items;

Revenue is computed per line, then summed

At Harbor & Pine each order line stores the quantity, the unit price at the time of sale and a discount in percent (15.0 means 15% off). Net revenue for a line is:

quantity * unit_price * (1 - discount_pct / 100.0)

Dividing by 100.0 rather than 100 is a portability habit: if discount_pct were an integer column, Postgres would compute 15 / 100 as 0 and quietly drop every discount.

Put the whole expression inside SUM. Do not multiply sums together: SUM(quantity) * SUM(unit_price) is a meaningless number. The rule is compute at the row level, aggregate once.

Notice that order_items.unit_price is the price paid, which can differ from today’s products.unit_price after a price change. Revenue always uses the price on the order line.

Excluding bad rows, out loud

A handful of lines have zero or negative quantities. Brian asked to exclude them, so the query has WHERE quantity > 0. On the job, always state exclusions in your reply (“excludes 24 lines with quantity <= 0”). When finance reconciles your number against theirs, that one sentence saves an hour.

How AVG treats NULLs

Like COUNT(column), SUM, AVG, MIN and MAX ignore NULLs. AVG(unit_cost) over products is the average of the known costs. That is usually right, but it means AVG(x) is not always SUM(x) / COUNT(*):

SELECT
  AVG(unit_cost) AS avg_known_cost,           -- ignores NULLs
  SUM(unit_cost) / COUNT(*) AS treats_null_as_0 -- different number
FROM products;

If every value is NULL, SUM returns NULL, not 0. Wrap it in COALESCE(SUM(x), 0) when a report needs a zero.

Averages of averages

A classic trap: the average order value is total revenue divided by number of orders, not the average of line revenues. Lines and orders are different grains, and an average depends on which one you average over. Always ask “one row per what?” before you write AVG.

Common mistakes

• Rounding inside the sum: SUM(ROUND(...)) adds up rounding errors. Round the final result.
• Using AVG(discount_pct) as “the average discount”. It weights a $5 line the same as a $900 line. A revenue-weighted discount is 100 * (1 - SUM(quantity * unit_price * (1 - discount_pct / 100.0)) / SUM(quantity * unit_price)).