Back to top
  Back to overview

Count functions

These functions can be used when counting field values and expressions.

Function Description
count() Counts a row, or whether a given value is truthy

count

The count function counts the number of times that the function is called. This is useful for returning the total number of rows in a SELECT statement with a GROUP BY clause.

count() -> 1

If a value is given as the first argument, then this function checks whether a given value is truthy. This is useful for returning the total number of rows, which match a certain condition, in a SELECT statement, with a GROUP BY clause.

count(value) -> number

If an array is given, this function counts the number of items in the array which are truthy. If, instead, you want to count the total number of items in the given array, then use the array::len() function.

count(array) -> number

The following examples show this function, and its output, when used in a select statement:

SELECT * FROM count();
1
SELECT * FROM count(true);
1
SELECT * FROM count(10 > 15);
0
SELECT * FROM count([1, 2, 3, null, 0, false, (15 > 10), rand::uuid()]);
4

The following examples show this function being used in a SELECT statement with a GROUP BY clause:

SELECT count() FROM [{ age: 33 }, { age: 45 }, { age: 39 }] GROUP BY ALL;
3
SELECT count(age > 35) FROM [{ age: 33 }, { age: 45 }, { age: 39 }] GROUP BY ALL;
2

An advanced example of the count function can be seen below:

SELECT
	country,
	count(age > 30) AS total
FROM [
	{ age: 33, country: 'GBR' },
	{ age: 45, country: 'GBR' },
	{ age: 39, country: 'USA' },
	{ age: 29, country: 'GBR' },
	{ age: 43, country: 'USA' }
]
GROUP BY country;
[
	{
		country: 'GBR',
		total: 2
	},
	{
		country: 'USA',
		total: 2
	}
]