Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Aggregate Functions

SQL TOTAL() function

Reference for SQLite TOTAL(): like SUM but returns 0.0 for no rows, always a float, with NULL handling and the portable COALESCE(SUM()) equivalent.

TOTAL(expr) is an aggregate function specific to SQLite. It adds up the non-NULL values of expr like SUM, but it always returns a floating-point number and returns 0.0 instead of NULL when there is nothing to add.

That one difference removes the need for COALESCE(SUM(x), 0) in SQLite. No other major database has TOTAL, so portable code should use COALESCE.

SQL
SELECT TOTAL(stock) AS units_in_stock
FROM books;

Try it

SQL SUM versus TOTAL on an empty set ยท database: bookstore

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

SQL Type of the result ยท database: bookstore

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

SQL Per-group totals, including groups of NULLs ยท database: company

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

Syntax

SQL
TOTAL(expr)
TOTAL(DISTINCT expr)
Part Description
expr The value to add. NULLs are ignored; text is converted to a number where possible.
DISTINCT Adds each distinct value once.

Returns: the sum as a floating-point number; 0.0 when there are no non-NULL values.

Dialect differences

Database Equivalent
SQLite TOTAL(x)
PostgreSQL COALESCE(SUM(x), 0)
MySQL COALESCE(SUM(x), 0)
SQL Server COALESCE(SUM(x), 0) or ISNULL(SUM(x), 0)
Oracle NVL(SUM(x), 0) or COALESCE(SUM(x), 0)
SQL
-- PostgreSQL, MySQL, SQL Server, Oracle
SELECT COALESCE(SUM(price), 0) AS total_price
FROM books
WHERE genre = 'Cookbook';

Usage notes

  • Never NULL. This is the reason to use it: reports and application code do not need a null check.
  • Always a float. SUM of integers stays an integer in SQLite; TOTAL converts it to a real, so TOTAL(stock) is the real number 76.0 where SUM(stock) is the integer 76 (the table viewer may print both as 76; typeof() tells them apart).
  • No integer overflow. SUM raises an "integer overflow" error if the total exceeds 64 bits; TOTAL does not, at the cost of rounding for very large values.
  • Precision. Adding many floating-point values accumulates rounding error. Do not use TOTAL for money held in a float column without rounding the result.
  • Not portable. Prefer COALESCE(SUM(x), 0) in code that must run on several databases.
  • A fully NULL group is also 0.0. That hides the difference between "no data" and "zero". Use SUM when you must tell them apart.