Counting and totals

What does an aggregate function do?

Every query so far returned one result row for each table row it kept. Aggregate functions do something different: they read many rows and return one value. COUNT counts, SUM adds, AVG averages, and MIN and MAX find the smallest and largest. People also call them summary functions.

When a SELECT uses aggregate functions and has no GROUP BY, the whole set of rows becomes one group, and the result is a single row. That is what turns ten orders into the one-line summary in the demo. Open the Rows view on the solution: the FROM step shows ten rows, and the SELECT step folds them into one.

Aggregates answer the questions owners actually ask. How many orders did we take this week? How many loaves went out? What is the average basket? The next lesson splits the rows into groups, so you can answer those questions per product or per day. This lesson is about getting one answer right.

SELECT COUNT(*) AS order_count, SUM(qty) AS items_sold
FROM orders;

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows. It does not look at any column, so a row full of NULLs still counts. On the orders table it returns 10.

COUNT(customer_email) counts the rows where customer_email is not NULL. Three orders were walk-ins with no email, so it returns 7. Both are correct; they answer different questions. The first is "how many orders?" and the second is "how many orders came from customers we can contact?".

Mixing them up is one of the most common reporting bugs, and it is quiet: the number looks plausible. When you write COUNT, say the question in words first. If the answer is "how many rows", use *. If it is "how many rows have a value for this", name the column.

SELECT COUNT(*) AS all_orders,
  COUNT(customer_email) AS with_email
FROM orders;

How do you count different values?

COUNT(DISTINCT column) counts each different non-NULL value once. COUNT(DISTINCT product_id) on the orders table returns 5: six products exist, five were ordered at least once. COUNT(DISTINCT customer_email) returns 4 different customers, because Mo and Li ordered more than once and walk-ins have no email.

DISTINCT inside COUNT only takes one column in SQLite. To count distinct pairs, you need techniques from later lessons. You can also use DISTINCT with SUM and AVG, but that is rarely what you want: SUM(DISTINCT qty) adds up each different quantity once, so two orders of 2 count as one.

SELECT COUNT(DISTINCT product_id) AS products_ordered,
  COUNT(DISTINCT customer_email) AS known_customers
FROM orders;

How do SUM and AVG work?

SUM(qty) adds the values in a column: 34 items across all orders. You can also sum an expression. SUM(qty * 2) would count halves, and later, once you can join tables, SUM(qty * price) gives revenue.

AVG(qty) adds the values and divides by how many there are: 34 divided by 10 is 3.4. In SQLite, AVG always returns a decimal number, even for a column of whole numbers. In PostgreSQL, AVG of integers returns a precise decimal; in SQL Server, AVG of an integer column returns a whole number, which surprises people.

Averages hide a lot. One order of 12 cinnamon buns pulls the average up for every other order. When the average looks odd, compare it with MIN, MAX and the count before you trust it.

SELECT SUM(qty) AS items_sold,
  AVG(qty) AS avg_qty,
  MAX(qty) AS biggest_order
FROM orders;

How do you round an average for display?

ROUND(value, places) rounds to a number of decimal places. ROUND(AVG(qty), 1) gives 3.4. With no second argument, it rounds to a whole number and still returns a decimal, such as 3.0.

Round at the last step, for display. If you round values before adding them up, the small errors add up too. For money, a common approach is to store whole pence or cents as integers, which never have rounding problems, and divide by 100 only when you show the result.

Floating-point numbers can show long tails such as 2.9000000000000004 after arithmetic. That is how computers store decimals, not a bug in SQLite. ROUND hides it in reports.

SELECT ROUND(AVG(price), 2) AS avg_price
FROM products;

How do MIN and MAX work on text and dates?

MIN and MAX work on any type that can be sorted. On numbers they give the smallest and largest. On text they give the first and last in alphabetical order. On YYYY-MM-DD dates they give the earliest and latest date, which is one more reason to store dates in that format.

MIN(ordered_on) and MAX(ordered_on) together tell you the range of dates a table covers. That is a good first query on any unfamiliar data: it tells you whether you are looking at a week, a year or something unexpected.

SQLite has a feature that other databases do not. If a query has exactly one MIN or MAX and also selects an ordinary column, the ordinary column comes from the row that held the minimum or maximum. That is handy, but it is specific to SQLite, so do not rely on it in queries you will move elsewhere.

SELECT MIN(ordered_on) AS first_day,
  MAX(ordered_on) AS last_day,
  MAX(qty) AS biggest_order
FROM orders;

What do aggregates do with NULL?

Every aggregate except COUNT(*) ignores NULLs. The Seasonal tart has no price yet. AVG(price) on the products table averages the five known prices and divides by 5, not 6. MIN and MAX skip it too.

Usually that is what you want. Sometimes it is not: if a missing price should count as zero, write AVG(COALESCE(price, 0)), and the average drops because it now divides by 6. Decide which question you are asking.

If every value is NULL, or no rows are left after WHERE, SUM, AVG, MIN and MAX return NULL, and COUNT returns 0. A report that says total sales: blank is usually the sign of a filter that removed every row. COALESCE(SUM(qty), 0) turns that into a zero.

SELECT COUNT(*) AS products,
  COUNT(price) AS priced,
  ROUND(AVG(price), 2) AS avg_known_price,
  ROUND(AVG(COALESCE(price, 0)), 2) AS avg_if_zero
FROM products;

Can you combine aggregates with WHERE?

Yes, and you will do it constantly. WHERE runs first and decides which rows go into the summary. SELECT SUM(qty) FROM orders WHERE product_id = 1 adds only the sourdough orders. In the Rows view, the WHERE step drops rows before the summary happens.

What you cannot do is put an aggregate inside WHERE. WHERE SUM(qty) > 10 fails with a misuse of aggregate error, because WHERE looks at one row at a time and a sum belongs to a whole group. Filtering on totals needs HAVING, which has its own lesson after grouping.

SELECT COUNT(*) AS sourdough_orders, SUM(qty) AS loaves
FROM orders
WHERE product_id = 1;

Field notes

• Aggregate functions read many rows and return one value.
• COUNT(*) counts rows; COUNT(column) skips rows where that column is NULL.
• SUM adds up a column and AVG gives its average, always as a decimal in SQLite.
• Every aggregate except COUNT(*) ignores NULL values.

Easy to get wrong

Counting a column skips its NULLs, so orders without an email go missing from the total.

Without DISTINCT, every order is counted, so a product ordered five times counts five times.

Aggregates cannot go in WHERE. Filter rows first, then summarise, or use HAVING for totals.

Rounding each row before adding lets rounding errors pile up. Round once, at the end.

Questions people ask

Why does SUM return NULL instead of 0?

No rows reached it, or every value was NULL. Wrap it as COALESCE(SUM(qty), 0) if a zero reads better. SQLite also has TOTAL(qty), which returns 0.0 in that case.

Can I mix an aggregate with a normal column?

Only with GROUP BY, which the next lesson covers. Without it, other databases reject the query and SQLite picks a value from an arbitrary row.

Is COUNT(1) faster than COUNT(*)?

No. They return the same number and run the same way. COUNT(*) is the plainest way to say "count rows".

Can I use an alias like items_sold in WHERE?

Not portably. WHERE runs before SELECT names its columns. Repeat the expression or filter in HAVING instead.