If you ever have a list of numbers in DuckDB that you want to add up, there are several ways to do this. By “add up” I mean, say you have a list that contains three numeric values. You can use the following methods to get a sum of all those values.
Option 1: The list_sum() Function
The list_sum() function is built specifically for adding up the values in a list.
Here’s an example:
SELECT list_sum([10, 12, 15]);Result:
37
So we simply pass the list to the function. No other arguments are required or accepted.
It works with fractions too:
SELECT list_sum([1.0, 1.2, 1.5]);Result:
3.7
Option 2: The list_aggregate() Function
The list_aggregate() function is a more general function that allows us to do the same thing. It accepts the list as its first argument, and the name of an aggregate function as its second. For example the second argument can be sum in order to use get the sum of the numbers in the list provided in the first argument:
SELECT list_aggregate([10, 12, 15], 'sum');Result:
37
So it’s a slightly less concise way of achieving the same output we got with the list_sum() function.
As with list_sum(), we can use list_aggregate() on fractions:
SELECT list_aggregate([1.0, 1.2, 1.5], 'sum');Result:
3.7
Option 3: The sum() Function
There’s also the option of unnesting the list and then applying sum() against the results:
SELECT sum(l)
FROM unnest([10, 12, 15]) AS t(l);Output:
37
The way this works is that the unnest() function returns each list item on a separate row. The sum() function can then do what it would normally do, which is add up the values in all rows.
To demonstrate what I mean, here it is without the sum() aggregate:
SELECT l
FROM unnest([10, 12, 15]) AS t(l);Output:
+----+
| l |
+----+
| 10 |
| 12 |
| 15 |
+----+
With this method, it’s possible to filter the list of numbers, such as only adding numbers over a certain value:
SELECT sum(l)
FROM unnest([10, 12, 15]) AS t(l)
WHERE l > 10;Output:
27
This example added only the values over ten (which in this case, is the last two values).