Please read the overview for important information on how to accelerate aggregate queries.

​
Count

COUNT tallies the number of matching rows/documents from the table.

SELECT COUNT(*) FROM mock_items
WHERE description @@@ 'shoes';

Because COUNT(*) does not specify any fields, you do not need to worry about passing a fast field to COUNT if * is used.

​
Average

AVG calculates the mean of the specified numeric field values across all documents.

SELECT AVG(rating) FROM mock_items
WHERE description @@@ 'shoes';

​
Sum

SUM computes the total sum of the specified numeric field values across all documents.

SELECT SUM(rating) FROM mock_items
WHERE description @@@ 'shoes';

​
Min

MIN finds the smallest value for the specified numeric field across all documents.

SELECT MIN(rating) FROM mock_items
WHERE description @@@ 'shoes';

​
Max

MAX finds the largest value for the specified numeric field across all documents.

SELECT MAX(rating) FROM mock_items
WHERE description @@@ 'shoes';

​
Cardinality

COUNT(DISTINCT) returns the number of unique values in a field. This is also known as a cardinality aggregation.

SELECT COUNT(DISTINCT rating) FROM mock_items
WHERE description @@@ 'shoes';

​
Histogram

The following query creates buckets of size 1 for the rating field. Each field value is rounded down to its bucket.

SELECT FLOOR(rating / 1) * 1 AS bucket, COUNT(*) AS count
FROM mock_items
GROUP BY bucket
ORDER BY bucket;

This query increases the bucket size to 2.

SELECT FLOOR(rating / 2) * 2 AS bucket, COUNT(*) AS count
FROM mock_items
GROUP BY bucket
ORDER BY bucket;

​
Other Aggregates

While the above list of aggregates is non-exhaustive, the answer to any kind of aggregate in ParadeDB is to just write SQL. Any aggregate that can be expressed in SQL can be written in ParadeDB.