Summarize with aggregate functions

Aggregate functions turn many input rows into one summary number. You use them when a report needs totals, averages, or counts instead of individual rows.

Suppose the paid orders table has totals 49.00, 18.50, and NULL. This query returns a single row:

SELECT
  COUNT(*) AS order_count,
  COUNT(total) AS priced_orders,
  SUM(total) AS revenue,
  AVG(total) AS average_total,
  MIN(total) AS smallest_total,
  MAX(total) AS largest_total
FROM orders
WHERE status = 'paid';

You should get:

order_count | priced_orders | revenue | average_total
3           | 2             | 67.50   | 33.75

Notice the difference between COUNT(*) and COUNT(total). COUNT(*) counts every row. COUNT(total) skips rows where total is NULL. SUM, AVG, MIN, and MAX ignore NULL inputs too.

This matters in real reports. A NULL total does not become zero. It means the value is missing. The average uses two known totals (49.00 and 18.50), not three orders. If you forget that rule, a dashboard can look healthy while half the orders have no price recorded.

When no rows match, COUNT(*) returns zero. The other aggregates usually return NULL. If your app needs a numeric zero, say that explicitly:

SELECT COALESCE(SUM(total), 0) AS revenue
FROM orders
WHERE status = 'refunded';

With no refunded orders, revenue shows 0 instead of NULL.

Filtering happens before aggregation. If the question is paid revenue, your WHERE clause must exclude pending and refunded rows first. Putting the filter in the wrong place changes every number in the result.

You can combine aggregates with GROUP BY when you need one summary per category. That pattern shows up in almost every dashboard. We cover grouping in the next lesson in this course. For now, practice reading aggregate output row by row.

Try this on your own: predict every aggregate result for three values (10, 20, and NULL). Then run one query and compare your prediction to the output.