How to Add Just the Distinct Values with SQLite Sum()

In SQLite, the Sum() function accepts an optional DISTINCT keyword that enables you to add just the distinct values in the group. That is, it removes any duplicates from its calculation.

So if there are say, three rows that contain 10, only one of those rows will be included in the results.

Syntax

The syntax when using the DISTINCT keyword goes like this:

Sum(DISTINCT X)

Where X is the name of the column for which values you’re adding.

Example

Consider the following table called Products:

ProductId   ProductName    Price     
----------  -------------  ----------
1           Widget Holder  139.5     
2           Blue Widget    10.0      
3           Red Widget     10.0      
4           Green Widget   10.0      
5           Widget Stick   89.75     
6           Foo Cap        11.99     

Below is an example of getting the distinct sum of all prices.

SELECT Sum(DISTINCT Price) 
FROM Products;

Result:

251.24

Here it is again, but this time I also include a “non-distinct” sum() for comparison’s sake.

SELECT 
  Sum(Price),
  Sum(DISTINCT Price) 
FROM Products;

Result:

Sum(Price)  Sum(DISTINCT Price)
----------  -------------------
271.24      251.24             

So the non-distinct sum(Price) added all values regardless of duplicates. But sum(DISTINCT Price) only added the distinct values.