CQL native aggregates
Works on a set of rows matching the SELECT statement to return a single value.
Synopsis
aggregate_name(column_name)
- AVG
AVG(column_name)
: Provides the average value of the target column using the data type of target column. Null values are ignored.Only works on numeric columns, that is tinyint, smallint, int, bigint, decimal, float, and double.
- COUNT
COUNT(column_name)
: 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.Works on all CQL data types columns.
- MIN
MIN(column_name)
: Provides the smallest value. Null values are ignored.Only works numeric columns, that is tinyint, smallint, int, bigint, decimal, float, and double.
- MAX
MAX(column_name)
: Provides the largest value. Null values are ignored.Only works numeric columns, that is tinyint, smallint, int, bigint, decimal, float, and double.
- SUM
SUM(column_name)
: Provides the total of the target column; nulls are ignored.Only works on numeric columns, that is tinyint, smallint, int, bigint, decimal, float, and double.
Examples
The following examples show how to use aggregates using the cyclist examples.
Find Average (AVG)
SELECT AVG(cyclist_time_sec) AS Average FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
average
---------
11474
(1 rows)
Find Count (COUNT)
SELECT count(cyclist_name) AS Row_Count FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
row_count ----------- 3 (1 rows)
Find lowest value (MIN)
SELECT MIN(cyclist_time_sec) AS Fastest FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
fastest
---------
11449
(1 rows)
Find highest value (MAX)
SELECT MAX(cyclist_time_sec) AS Slowest FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
slowest
---------
11490
(1 rows)
Find total (SUM)
SELECT SUM(cyclist_time_sec) AS Total_Time FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
total_time
------------
34424
(1 rows)