SQLite Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

What an Aggregate Actually Does

Most SQL functions you've met so far operate row-by-row: UPPER(name) runs once per row, ROUND(price, 2) runs once per row. Aggregate functions are different. They look at a whole set of rows and collapse it down to a single value.

Set up a small table to play with:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

Five rows go in, one row comes out. That's the whole mental model: aggregates squash rows into a summary. Without a GROUP BY, the summary covers every row in the result.

COUNT: Rows vs Values

COUNT has three forms, and the difference matters:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)               AS rows_total,
    COUNT(amount)          AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

• COUNT(*) counts rows. NULLs included. Always returns a number.
• COUNT(column) counts non-NULL values in that column.
• COUNT(DISTINCT column) counts unique non-NULL values.

Five rows, three of them with an amount, three distinct customers. If you ever see COUNT(amount) and wonder why it's smaller than COUNT(*), that's why — NULLs aren't counted.

SUM, AVG, MIN, MAX

The arithmetic aggregates work as you'd expect, with one quiet rule: they all skip NULL:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount) AS total,
    AVG(amount) AS mean,
    MIN(amount) AS smallest,
    MAX(amount) AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG is (10 + 20 + 30) / 3 = 20.0, not 60 / 4 = 15.0. The denominator is the count of non-NULL values. If that's not what you want — if you'd rather treat missing data as zero — be explicit:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN and MAX work on text and dates too, comparing lexicographically for text and as ISO strings for the standard date format.

SUM vs TOTAL

SQLite has a second sum-like aggregate, TOTAL, that fixes two annoyances of SUM:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

• SUM of zero rows returns NULL. TOTAL returns 0.0.
• SUM of all-NULL values returns NULL. TOTAL returns 0.0.
• TOTAL always returns a floating-point number, so it never overflows integer arithmetic.

The trade-off: TOTAL is non-standard, and the always-REAL result can surprise you if you were expecting an integer. Reach for it when "no rows means zero" is the right answer for your app, and stick with SUM when you want SQL-standard behaviour.

DISTINCT Inside Aggregates

DISTINCT can go inside any aggregate, not just COUNT. It removes duplicate values before the aggregation runs:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)          AS sum_all,
    SUM(DISTINCT amount) AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) adds every row's amount. SUM(DISTINCT amount) adds each unique amount once — useful for things like "total of unique invoice amounts" but rarely what you want. COUNT(DISTINCT customer) is the common one.

FILTER: Aggregating a Subset

When you want to aggregate only some of the rows, the obvious move is WHERE. But WHERE filters everything — you can't combine "count paid orders" and "count refunds" in the same query that way. FILTER solves it:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*) FILTER (WHERE status = 'paid')      AS paid_count,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')   AS paid_revenue
FROM orders;

Each FILTER (WHERE ...) clause applies only to that one aggregate. One pass over the table, multiple slices summarized. Before FILTER existed, people wrote SUM(CASE WHEN status = 'paid' THEN amount END) — same idea, more typing.

GROUP_CONCAT: Joining Strings

GROUP_CONCAT is the odd one out. Instead of returning a number, it concatenates the values into a single string:

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

The default separator is a comma. Pass a second argument to use something else. Order isn't guaranteed unless you wrap the call as GROUP_CONCAT(tag ORDER BY tag) — handy when the output appears in a UI and you want it stable.

Aggregating Without GROUP BY

Every example so far that used aggregates without GROUP BY produced exactly one row. That's the rule: a SELECT with aggregates and no GROUP BY is a single-row summary of the whole table (after WHERE).

You can mix aggregates freely:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

What you can't do is mix non-aggregated columns with aggregates and expect sensible results:

-- Allowed by SQLite, but the value of `customer` is arbitrary.
SELECT customer, SUM(amount) FROM orders;

SQLite won't error here (other databases will), but it'll pick some random customer's name to show alongside the total. If you want a sum per customer, you need GROUP BY — which is the next page.

Next: GROUP BY and HAVING

Aggregates over the whole table answer "how much in total." Aggregates per group — per customer, per month, per status — answer the more interesting questions. GROUP BY is how you split the rows into buckets before aggregating, and HAVING is how you filter on the aggregated result. That's next.