Common Aggregates

​

Count

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

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

​

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;

 bucket | count
--------+-------
      1 |     1
      2 |     3
      3 |     9
      4 |    16
      5 |    12
(5 rows)

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;

 bucket | count
--------+-------
      0 |     1
      2 |    12
      4 |    28
(3 rows)

​

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.