The SQLite sum() function returns the sum of all non-NULL values in a group.

If there are no non-NULL values, then it returns NULL.

This function basically enables you to add up all the values in a result set or a table.

Example

Here’s an example to demonstrate its usage.

SELECT sum(Price) FROM Products;

Result:

738.22

This example is probably meaningless if you can’t see the actual values in the table.

Here’s a query that returns all rows in that 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                    

So the sum() function simply added all the values in the Price column.

Notice that the last row’s price is NULL. That’s OK. The sum() function simply adds all the non-NULL values.

NULL Values

If there are no non-NULL values, then the result is NULL.

SELECT sum(Price) 
FROM Products
WHERE ProductId = 5;

Result:

(That’s intentionally blank, because it returned NULL).

This is different to how the SQLite total() function deals with NULL values (it returns 0.0). Both sum() and total() do the same thing, except for this difference.

Smaller Result Set

The first example added all rows in the table. But it doesn’t have to be the whole table. As seen in the “NULL” example, you can also use a WHERE clause to add a subset of values in the table.

Here’s another example that uses a WHERE clause.

SELECT sum(Price) 
FROM Products
WHERE ProductId < 3;

Result:

528.95

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 sum(Price) 
FROM Products
WHERE Price > 100;

Result:

648.95

Non Integer Inputs

If any input is neither an integer or NULL then sum() returns a floating point value which might be an approximation to the true sum.

You might get some unexpected results in such cases.

Below is an example that attempts to add a bunch of fax numbers.

First, let’s look at the list of fax numbers.

SELECT Fax FROM Customer 
WHERE Fax IS NOT NULL;

Result:

Fax               
------------------
+55 (12) 3923-5566
+420 2 4172 5555  
+55 (11) 3033-4564
+55 (11) 3055-8131
+55 (21) 2271-7070
+55 (61) 3363-7855
+1 (780) 434-5565 
+1 (604) 688-8756 
+1 (650) 253-0000 
+1 (425) 882-8081 
+1 (212) 221-4679 
+1 (408) 996-1011 

Now here’s what happens if I try to add them up.

SELECT sum(Fax) 
FROM Customer;

Result:

701.0 

In this case it appears to have added all the prefixes.

Here’s what happens if I use sum() on a column of strings.

SELECT sum(ProductName) 
FROM Products;

Result:

0.0

The DISTINCT Keyword

You can add the DISTINCT keyword to add only distinct values. To do this, use sum(DISTINCT X) where X is the column name.

See How to Add Just the Distinct Values with SQLite Sum() for an example.