In SQLite, the total()
function returns the sum of all non-NULL values in a group.
If there are no non-NULL values, then it returns 0.0.
This function is similar to the sum()
function, except in the way it handles NULL inputs. When there are no non-NULL values, then sum()
returns NULL (instead of 0.0 like the total()
function returns).
Example
Consider the following table.
SELECT * FROM Products;
Result:
ProductId ProductName Price ---------- --------------------- ---------- 1 Blue Widgets (6 Pack) 389.45 2 Widget Holder 139.5 3 Widget Opener 89.27 4 Foobar Set 120.0 5 Red Widget
We can use the total()
function to get a total of the prices.
SELECT total(Price) FROM Products;
Result:
738.22
Even though the last row’s price is NULL, the total()
function can handle that, because it adds all the non-NULL values and ignores the NULL value.
All NULL Values
If there are no non-NULL values, then the result is 0.0.
SELECT total(Price)
FROM Products
WHERE ProductId = 5;
Result:
0.0
This is where total()
and sum()
differ. In this case, sum()
would have returned NULL.
The total()
function always returns a floating point value.
Bear in mind that this does not conform to the SQL standard though. If you want to use standard compliant code, use sum()
.
Get the Total of a Subset
Here’s another example that uses a WHERE
clause to return a subset of the table.
SELECT total(Price)
FROM Products
WHERE ProductId > 2;
Result:
209.27
This time it’s adding non-NULL values, so I get a non-NULL result.
Here’s another one that filters by the same column that I’m adding.
SELECT total(Price)
FROM Products
WHERE Price < 150;
Result:
348.77