When working with lists in DuckDB, it’s often useful to know how many elements you’re working with. Fortunately DuckDB provides us with several functions that help us determine this. Below are three options for counting up the elements in a list when using DuckDB.

1. The list_count() Function

The list_count() function is an obvious one. It’s sole purpose is to count the number of elements in a list. All we need to do is pass the list to the function:

SELECT list_count(['Cat', 'Dog', 'Duck']);

Result:

3

In this example I passed a list that contained three elements, and so the function returned 3. The function doesn’t require or accept any other arguments, so this is a quick and easy method for counting list elements.

2. The list_aggregate() Function

The list_aggregate() function allows us to perform an aggregate operation on a list based on a specified function. What I mean is that we pass the list as the first argument, and the name of an aggregate function as the second. The list_aggregate() function then behaves as if it were the function that we specified in the second argument.

Here’s an example to demonstrate:

SELECT list_aggregate(['Cat', 'Dog', 'Duck'], 'count');

Result:

3

Same result as the previous example. In this case I passed count as the function to perform. The list_aggregate() function understood what I wanted and performed the count operation.

One benefit of list_aggregate() is that we can use it to perform multiple functions. For example, we could specify sum as the second argument to add up the numbers in a list. Or we could use string_agg to concatenate the strings in a list.

3. The count() Function

We can also use the count() function itself to count the number of elements in a list. However, we’ll need to unnest the list first:

SELECT count(*)
FROM unnest(['Cat', 'Dog', 'Duck']);

Result:

3

As expected, same result again.

This option requires a bit more work to get the result. That’s because we had to unnest the list before performing the aggregate operation.

Here’s what it looks like without the count() function:

SELECT *
FROM unnest(['Cat', 'Dog', 'Duck']);

Result:

+--------+
| unnest |
+--------+
| Cat    |
| Dog    |
| Duck   |
+--------+

So the count() function counted up the number of rows after the list was unnested.

You may be wondering why I didn’t just unnest the list inside the count() function itself? Here’s what happens if I do that:

SELECT count(unnest(['Cat', 'Dog', 'Duck']));

Output:

Binder Error:
UNNEST not supported here

LINE 1: SELECT count(unnest(['Cat', 'Dog', 'Duck']));
                     ^

And you may be wondering why we can’t just put the raw list there? Well, we can. But here’s what happens when we do that:

SELECT count(['Cat', 'Dog', 'Duck']);

Output:

1

It doesn’t count the actual elements in the list.

We can actually omit the asterisk when using count(). So we can use count() instead of count(*) if we want:

SELECT count()
FROM unnest(['Cat', 'Dog', 'Duck']);

Result:

3

Another thing we can do is count up the distinct elements in the list. Here’s an example:

SELECT 
    count(l), 
    count(distinct l)
FROM unnest(['Cat', 'Dog', 'Duck', 'Duck']) AS t(l);

Result:

+----------+-------------------+
| count(l) | count(DISTINCT l) |
+----------+-------------------+
| 4        | 3                 |
+----------+-------------------+

The first column counted all elements, while the second column counted the distinct elements (i.e. any duplicate values were counted as one).