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

Aggregate	Supported data types	Description
`AVG`	tinyint, smallint, int, bigint, decimal, float, and double.	Provides the average value of the target column using the data type of target column. Null values are ignored.
`COUNT`	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.
`MIN`	tinyint, smallint, int, bigint, decimal, float, and double.	Provides the smallest value. Null values are ignored.
`MAX`	tinyint, smallint, int, bigint, decimal, float, and double.	Provides the largest value. Null values are ignored.
`SUM`	tinyint, smallint, int, bigint, decimal, float, and double.	Provides the total of the target column; nulls are ignored.

AVG

tinyint, smallint, int, bigint, decimal, float, and double.

Provides the average value of the target column using the data type of target column. Null values are ignored.

COUNT

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.

MIN

tinyint, smallint, int, bigint, decimal, float, and double.

Provides the smallest value. Null values are ignored.

MAX

tinyint, smallint, int, bigint, decimal, float, and double.

Provides the largest value. Null values are ignored.

SUM

tinyint, smallint, int, bigint, decimal, float, and double.

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)

CQL native aggregates

Synopsis

Examples

Find Average (AVG)

Find Count (COUNT)

Find lowest value (MIN)

Find highest value (MAX)

Find total (SUM)

Was this helpful?

Give Feedback