Count

This function can be used when counting field values and expressions.

count

The count function counts the number of times that the function is called. In a SELECT that aggregates with GROUP BY or GROUP ALL, that call count is the size of each group. Without a GROUP BY or GROUP ALL clause there is no group to size, so count() returns 1 once per record.

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 records which match a certain condition in a SELECT with a GROUP BY or GROUP ALL clause.

count(any) -> 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 example shows this function, and its output:

count();

1

count(true);

1

count(10 > 15);

0

count([ 1, 2, 3, null, 0, false, (15 > 10), rand::uuid() ]);

5

The following examples show this function being used in a SELECT statement with a GROUP ALL clause.

SELECT 
	count() 
FROM [
	{ age: 33 }, 
	{ age: 45 }, 
	{ age: 39 }
] 
GROUP ALL;

[
	{ count: 3 }
]

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

[
	{ count: 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
	}
]

Counting the records that link to each record

To count the records that link to each record, such as the comments on each post, count inside the projection. Where the link field is defined with REFERENCE, <~comment gives the comments that link to the current post:

DEFINE FIELD post ON comment TYPE record<post> REFERENCE;

CREATE post:a, post:b, post:c;
CREATE comment SET post = post:a;
CREATE comment SET post = post:a;
CREATE comment SET post = post:b;

SELECT id, count(<~comment) AS comments FROM post;

[
	{ comments: 2, id: post:a },
	{ comments: 1, id: post:b },
	{ comments: 0, id: post:c }
]

Without REFERENCE, a subquery gives the same result, with $parent as the current post:

SELECT id, (SELECT VALUE id FROM comment WHERE post = $parent.id).len() AS comments FROM post;

Grouping the comments instead, with SELECT post, count() FROM comment GROUP BY post, gives the same counts but leaves out every post that has no comments, because no comment carries its ID.

Using a COUNT index with count()

Available since: v3.0.0

A COUNT index can be defined to speed up count() when used with a GROUP ALL clause. This allows count() to access a single stored value when it is called instead of iterating over the entire table.

CREATE user;
-- One record in table, very fast
SELECT count() FROM user GROUP ALL;

-- 10,000 new records,
-- count() takes a bit longer than before
CREATE |user:10000| RETURN NONE;
SELECT count() FROM user GROUP ALL;

-- Add index, wait a moment for it to build
DEFINE INDEX user_count ON user COUNT;
-- count() very performant again
SELECT count() FROM user GROUP ALL;