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.
SELECT TOTAL(stock) AS units_in_stock FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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) |
-- 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.
SUMof integers stays an integer in SQLite;TOTALconverts it to a real, soTOTAL(stock)is the real number 76.0 whereSUM(stock)is the integer 76 (the table viewer may print both as 76;typeof()tells them apart). - No integer overflow.
SUMraises an "integer overflow" error if the total exceeds 64 bits;TOTALdoes not, at the cost of rounding for very large values. - Precision. Adding many floating-point values accumulates rounding error. Do not use
TOTALfor 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
NULLgroup is also0.0. That hides the difference between "no data" and "zero". UseSUMwhen you must tell them apart.