3 Ways to Get the Average From a List of Numbers in DuckDB

by

When presented with a list of numbers, there are many things we might want to do with it. One is to get the average value of all values in the list. Fortunately DuckDB provides us with several ways to do this.

1. The list_avg() Function

DuckDB has a list_avg() function that’s designed specifically for calculating the average value in a list of values. We simply pass the list to the function, and it returns the average of all values in that list. For example:

SELECT list_avg([10, 12, 15]);

Result:

12.333333333333334

It works with fractions too:

SELECT list_avg([1.0, 1.2, 1.5]);

Result:

1.2333333333333334

2: The list_aggregate() Function

We can do the same thing with the list_aggregate() function. This is a more general function that can perform various aggregate operations. It accepts the list as its first argument, and the name of an aggregate function as its second. Passing avg as the second argument results in the average being calculated on the list provided in the first argument:

SELECT list_aggregate([10, 12, 15], 'avg');

Result:

12.333333333333334

Same output we got with the list_avg() function, except slightly less concise.

We can use list_aggregate() on fractions too:

SELECT list_aggregate([1.0, 1.2, 1.5], 'avg');

Result:

1.2333333333333334

Option 3: The avg() Function

There’s also the option of unnesting the list and then applying avg() against the results:

SELECT avg(l)
FROM unnest([10, 12, 15]) AS t(l);

Output:

12.333333333333334

Here, the unnest() function returns each list item on a separate row. The avg() function can then do what it would normally do, which is calculate the average of all values in those rows.

Here it is without the avg() 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 avg(l)
FROM unnest([10, 12, 15]) AS t(l)
WHERE l > 10;

Output:

13.5

This example included only the values over ten (which in this case, is the last two values).