SQLite 3 reference · Aggregate Functions

SQLite SUM() Function

Add up the non-NULL values in a group. Returns NULL when there are no values; total() returns 0.0 instead.

SQLite 3

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

sum(X)  |  sum(DISTINCT X)

Parameter	Description
`X`	A column or expression.

An integer if every value is an integer, otherwise a real number; NULL for no rows.

Total per orderTry it yourself

SELECT order_id, sum(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
ORDER BY order_total DESC
LIMIT 3;

Output

order_id | order_total
9 | 658
4 | 408.9
7 | 114.99

With a FILTER clauseTry it yourself

SELECT sum(stock) AS units, sum(price) FILTER (WHERE category_id = 1) AS book_prices
FROM products;

Output

units | book_prices
495 | 109.49

TOTAL() · Add up the non-NULL values in a group like sum(), but always return a real number and 0.0 for no rows.
AVG() · Return the average of the non-NULL values in a group, always as a real number. NULLs are ignored, not counted as 0.
COUNT() · Count rows: count(*) counts every row, count(X) counts rows where X isn't NULL, count(DISTINCT X) counts unique values.
SQL Aggregate Functions · tutorial
SQL GROUP BY · tutorial