Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 125 ยท Aggregation

Aggregate Functions

Summarize whole tables with COUNT, SUM, AVG, MIN and MAX, learn how each one treats NULL, and combine values into lists with group_concat and string_agg.

Every function you've used so far works row by row: upper(name) takes one row's name and returns one result for that row. Aggregate functions work differently. They look at many rows at once and collapse them into a single value: how many orders were placed, the total revenue, the average salary, the earliest hire date.

That's the heart of reporting. Questions like "how much did we sell?" or "what's our average delivery cost?" are all answered by aggregates. In this lesson you'll learn the five standard aggregates (COUNT, SUM, AVG, MIN and MAX), how each one treats NULL, SQLite's total(), and how to combine many values into one string with group_concat() and string_agg(). You'll also learn the rule that trips up every beginner: what you can and can't put next to an aggregate.

Here aggregates summarize the whole result of a query. In the next lesson, GROUP BY, you'll compute them per group: per department, per month, per customer.

Your first aggregates

An aggregate goes in the SELECT list like any other expression. Without GROUP BY, the query returns exactly one row, no matter how many rows the table has:

SQL Summarizing salaries ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The company has 36 employees with a yearly payroll of 3,253,000. The average is 90361.11111111111; wrap it in round(avg(salary), 2) for a report, as you learned in Numeric Functions.

Function Returns Works on
count(*) number of rows any rows
count(expr) number of non-NULL values any type
sum(expr) total numbers
avg(expr) arithmetic mean, always a real numbers
min(expr) / max(expr) smallest / largest value numbers, text, dates

Aggregates and WHERE

WHERE runs before the aggregate, so the aggregate only sees the rows that pass the filter. That lets you summarize any subset of a table. In the shop database, order_items has one row per product in an order:

SQL Sales totals ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Notice that aggregates accept expressions, not just columns: sum(quantity * unit_price) multiplies for each row first, then adds up all the products, giving 31,244.85. The second query only considers the 57 delivered orders.

COUNT in three flavors

count() is the most used aggregate, and its three forms answer three different questions:

  • count(*) counts rows, whatever they contain.
  • count(column) counts rows where that column is not NULL.
  • count(DISTINCT column) counts different non-NULL values.
SQL Three kinds of count ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

There are 36 employees but only 33 email addresses, because three employees have NULL there. Two employees (the CEO and a contractor) have no department, so count(department_id) is 34, and those 34 are spread across 6 distinct departments. The difference between count(*) and count(column) is a quick way to find how many values are missing. See DISTINCT for more on removing duplicates.

How aggregates treat NULL

All aggregates except count(*) ignore NULL values. They don't treat them as zero; they skip them as if those rows weren't there. Usually that's what you want, but it has consequences you should know:

SQL NULLs and empty results ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

In the first query, one of the seven projects has no budget, so avg(budget) divides by 6, not 7: it's the average of the known budgets (217,500).

The second query matches no rows at all, since there is no department 99. count(*) correctly says 0, but sum(), avg() and max() return NULL: there was nothing to add up. That's standard SQL, and it surprises many people. Two fixes:

  • COALESCE(sum(salary), 0) works in every database (see COALESCE, IFNULL and NULLIF).
  • SQLite's own total(salary) returns 0.0 for no rows. total() always returns a real, and it never fails with an integer overflow the way sum() can on huge integer totals.

MIN and MAX on text and dates

min() and max() work on any values that can be sorted, not only numbers. On text they use alphabetical order, and on ISO dates they find the earliest and latest:

SQL MIN and MAX beyond numbers ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The earliest hire was on 2016-03-01, and max(salary) - min(salary) combines two aggregates in one expression. Dates only compare correctly in the ISO YYYY-MM-DD format, as the Date and Time Functions lesson explained, and numbers stored as text compare as text (see Types and CAST).

Don't confuse these with the multi-argument forms from the numeric lesson: max(a, b) compares values within one row, while max(column) looks down the whole column.

Combining values into a list

Sometimes you don't want a number but a list: all the names in a team as one comma-separated string. SQLite has group_concat(value, separator), and since version 3.44 also the standard-style name string_agg(value, separator). Both accept an ORDER BY inside the parentheses to control the order of the list:

SQL group_concat and string_agg ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Without ORDER BY, the list comes out in whatever order SQLite reads the rows, and the default separator is a comma with no space. DISTINCT removes repeated values, so the ten engineering employees produce just three titles. (With DISTINCT, SQLite's group_concat only allows the default separator.) NULL values are skipped, like in every other aggregate.

To produce a JSON array instead of a string, use json_group_array() from Working with JSON.

Counting conditionally with FILTER

What if you want several different counts in one query, say high earners and low earners? The standard FILTER clause (supported by SQLite since 3.30 and by PostgreSQL) restricts one aggregate to the rows that match a condition:

SQL Aggregates with FILTER ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Eight people earn 100,000 or more. The last column gets the same answer with the CASE technique from CASE Expressions, which works in every database, including MySQL and SQL Server, where FILTER isn't available.

The bare column rule

Here's the rule that matters most: in a query with aggregates and no GROUP BY, the result has one row, so every expression in SELECT must produce one value for the whole set. A plain column like first_name has 36 different values. Which one should the single row show?

Most databases refuse to guess. PostgreSQL and SQL Server raise an error such as column "employees.first_name" must appear in the GROUP BY clause or be used in an aggregate function, and so does MySQL in its default mode. SQLite is unusually permissive: it accepts these "bare columns" and fills them from one of the rows.

SQL Bare columns in SQLite ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query happens to be useful: SQLite documents that with a single min() or max(), bare columns come from the row that holds that minimum or maximum, so Kofi Okafor really is the top earner. The second query is meaningless: Kofi is just whichever row SQLite looked at, not "the" first name of 36 employees. Because this behavior is SQLite-only and easy to misread, avoid bare columns. Use GROUP BY to get one row per group, or ORDER BY salary DESC LIMIT 1 to find the top row.

Aggregates can't go in WHERE

WHERE decides which rows go into the aggregate, so it runs before any aggregate exists. Using an aggregate inside WHERE is an error. This example fails on purpose:

SQL An aggregate in WHERE fails ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQLite stops with Error: misuse of aggregate function avg(). There are two correct tools, both coming soon:

  • To compare rows with an aggregate, compute the aggregate in a subquery: WHERE salary > (SELECT avg(salary) FROM employees). See Subqueries.
  • To filter groups by an aggregate, such as departments with more than five people, use HAVING.

Extra statistics

Standard SQL stops at the five classic aggregates plus string aggregation, but many databases add statistical ones: PostgreSQL has stddev(), variance() and percentile_cont(), and SQL Server has STDEV() and VAR(). Standard SQLite has none of these, though the SQLite build on this site includes an extension with median(), stdev() and variance(). Because they aren't in normal SQLite, this course computes medians and percentiles with window functions in Percentiles, Medians and Statistics.

Aggregates in other databases

Task SQLite PostgreSQL MySQL SQL Server Oracle
Join strings group_concat, string_agg string_agg GROUP_CONCAT(x ORDER BY y SEPARATOR ', ') STRING_AGG(x, ', ') WITHIN GROUP (ORDER BY y) LISTAGG(x, ', ') WITHIN GROUP (ORDER BY y)
Sum of no rows NULL (total() gives 0.0) NULL NULL NULL NULL
avg() of integers real numeric (exact) decimal integer (truncated) number
FILTER (WHERE ...) yes yes no no no
Bare columns allowed error error (default mode) error error

The SQL Server row deserves attention: AVG() of an integer column returns an integer there, so the average of 1 and 2 is 1. Write AVG(salary * 1.0) or AVG(CAST(salary AS decimal(12, 2))) to keep the fraction.

SQL
-- PostgreSQL
SELECT string_agg(first_name, ', ' ORDER BY first_name) FROM employees;

-- MySQL
SELECT GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') FROM employees;

-- SQL Server
SELECT STRING_AGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name), AVG(salary * 1.0)
FROM employees;

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A tickets table has 10 rows, and 4 of them have closed_at NULL. What does SELECT count(closed_at) FROM tickets; return?

    count(column) counts only non-NULL values, so the 4 open tickets are skipped. count(*) would return 10.
  2. 2. The score column holds 10, 20 and NULL. What does avg(score) return?

    avg ignores NULL, so it averages only 10 and 20. It does not treat the NULL as zero (which would give 10).
  3. 3. What does SELECT sum(salary) FROM employees WHERE department_id = 99; return when no employee is in department 99?

    An aggregate query without GROUP BY always returns one row. With no input rows, sum() is NULL; use COALESCE(sum(salary), 0) or SQLite's total() to get 0.
  4. 4. Why does WHERE salary > avg(salary) fail?

    WHERE filters individual rows before any aggregation. Compare with a subquery, (SELECT avg(salary) FROM employees), or filter groups with HAVING.
  5. 5. Which query returns all design team first names as one alphabetical, comma-separated string?

    group_concat (or string_agg) combines values from many rows into one string; the ORDER BY inside the parentheses sorts the list.

Summary

  • Aggregate functions turn many rows into one value; without GROUP BY, an aggregate query returns exactly one row.
  • count(*) counts rows, count(col) counts non-NULL values, and count(DISTINCT col) counts different values.
  • sum(), avg(), min() and max() skip NULLs; over zero rows they return NULL, so use COALESCE(sum(x), 0) or SQLite's total().
  • min() and max() also work on text and ISO dates.
  • WHERE filters rows before aggregation, and aggregates can't appear in WHERE.
  • group_concat() / string_agg() join values into a string, with optional ORDER BY and DISTINCT.
  • FILTER (WHERE ...) or sum(CASE ...) computes conditional aggregates.
  • Avoid bare columns next to aggregates: SQLite allows them, other databases reject them.

Next: GROUP BY, where you'll calculate these aggregates for every group of rows.