CQL native aggregates
Aggregate functions work on a set of rows matching a SELECT statement to return a single value. Null values in the data set are ignored. When other columns, columns with user-defined types, or functions, are also specified in the selector clause of a SELECT statement with an aggregate function, the values in the first row matching the query are returned.
|
Aggregating data across partitions may cause performance issues. The recommendation is to use aggregates on one partition, to get a list of partition keys use SELECT DISTINCT partition_key to get a list of unique keys (for compound keys use a comma separated list of column names). |
|
Define custom aggregates using CREATE AGGREGATE. |
Synopsis
<aggregate_name>(<column_name>)Where the system aggregate names are listed below:
| Aggregate | Supported data types | Description |
|---|---|---|
Provides the average value of the target column using the data type of target column. Null values are ignored. |
||
Works on all CQL data types columns |
Provides the number of rows in the result set that do not have null values. For a complete row count use partition key column, since partition keys cannot contain nulls. |
|
Provides the smallest value. Null values are ignored. |
||
Provides the largest value. Null values are ignored. |
||
Provides the total of the target column; nulls are ignored. |
Examples
Find Average (AVG)
Get average time in seconds for the team:
SELECT AVG(cyclist_time_sec) AS Average FROM cycling.team_average
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';Results
average
---------
11474
(1 rows)Find Count (COUNT)
Find the number rows for the United Health Care Pro Cycling Women’s Team:
SELECT COUNT(cyclist_name) AS Row_Count FROM cycling.team_average
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';Results
row_count
-----------
3
(1 rows)Find lowest value (MIN)
Find the slowest time recorded for the United Health Care Pro Cycling Women’s Team:
SELECT MIN(cyclist_time_sec) AS Fastest FROM cycling.team_average
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';Results
fastest
---------
11449
(1 rows)Find highest value (MAX)
Find the fastest time recorded for the United Health Care Pro Cycling Women’s Team:
SELECT MAX(cyclist_time_sec) AS Slowest FROM cycling.team_average
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';Results
slowest
---------
11490
(1 rows)Find total (SUM)
Find the total of all times recorded for the United Health Care Pro Cycling Women’s Team:
SELECT SUM(cyclist_time_sec) AS Total_Time FROM cycling.team_average
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';Results
total_time
------------
34424
(1 rows)