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.