COUNT, SUM, AVG, MIN, MAX

Part of the free LogicWiz SQL & Databases course, Module 4: Aggregates and GROUP BY. The queries and practice run in the app on real Postgres.

An investor asks Aarav three questions: how many orders, how much in delivery fees, and the average restaurant rating. He scrolls through 24 rows with a calculator. One line of SQL answers all three.

You'll learn to

• Squeeze many rows into one answer with COUNT, SUM, AVG, MIN and MAX
• Tell COUNT(*), COUNT(column) and COUNT(DISTINCT column) apart
• Predict what NULLs do to AVG and what SUM gives on no rows

Learn

Many rows in, one row out

So far every query gave one result row per table row. An aggregate function is different: it reads a whole column and gives back one value.

• COUNT(*): how many rows
• SUM(x): add them up
• AVG(x): the average
• MIN(x) / MAX(x): smallest and largest (works on numbers, dates and text)

WHERE runs first and picks the rows; the aggregate then summarises only those rows.

The menu in one row

SELECT COUNT(*) AS items,
  MIN(price) AS cheapest,
  MAX(price) AS costliest,
  SUM(price) AS whole_menu,
  ROUND(AVG(price), 2) AS avg_price
FROM menu_items;

AVG gives a long decimal like 223.2777777777777778, so wrap it in ROUND(…, 2) for a report.

Three kinds of COUNT

SELECT COUNT(*) AS all_orders,
  COUNT(partner_id) AS with_partner,
  COUNT(DISTINCT partner_id) AS partners_used,
  COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;

COUNT(*) counts rows. COUNT(partner_id) counts rows where partner_id is not NULL: three orders have no partner. COUNT(DISTINCT partner_id) counts different non-NULL values.

NULLs are skipped, not counted as zero

SELECT COUNT(*) AS restaurants,
  COUNT(rating) AS rated,
  ROUND(AVG(rating), 2) AS avg_of_rated,
  ROUND(AVG(COALESCE(rating, 0)), 2) AS avg_if_null_is_zero
FROM restaurants;

Pizza Corner has no rating. AVG(rating) divides the total by 7 (the rated ones), not 8. If the business says "no rating counts as 0", you must say so with COALESCE, and the answer drops a lot.

On zero rows, COUNT gives 0 but SUM, AVG, MIN and MAX give NULL, not 0. A report that shows "Fees today: NULL" looks broken. Write COALESCE(SUM(delivery_fee), 0).

You cannot mix one row with many

SELECT name, COUNT(*)
FROM restaurants;

COUNT(*) gives one row, but there are 8 names. Postgres refuses to guess which name to show. To get a count per something, you need GROUP BY (next lesson).

"What is the difference between COUNT(*), COUNT(col) and COUNT(DISTINCT col)?" is one of the most asked SQL interview questions. Answer: all rows; rows where col is not NULL; different non-NULL values. Also asked: "Does AVG count NULLs?" No, they are left out of both the sum and the count.

In one line

COUNT(*) counts rows, COUNT(col) skips NULLs, COUNT(DISTINCT col) counts different values; AVG ignores NULLs; SUM of no rows is NULL.