Aggregating results
Using the SELECT command to return data and apply standard aggregate functions.
Starting with DataStax Enterprise 5.0, the standard aggregate functions of
min
, max
, avg
,
sum
, and count
are built-in functions.
Procedure
-
A table cyclist_points records the race points for
cyclists.
CREATE TABLE cycling.cyclist_points (id UUID, firstname text, lastname text, race_title text, race_points int, PRIMARY KEY (id, race_points );
-
Calculate the standard aggregation function
sum
to find the sum of race points for a particular cyclist. The value of the aggregate will be returned.SELECT sum(race_points) FROM cycling.cyclist_points WHERE id=e3b19ec4-774a-4d1c-9e5a-decec1e30aac;
-
Another standard aggregate function is
count
. A table country_flag records the country of each cyclist.CREATE TABLE cycling.country_flag (country text, cyclist_name text, flag int STATIC, PRIMARY KEY (country, cyclist_name));
-
Calculate the standard aggregation function
count
to find the number of cyclists from Belgium. The value of the aggregate will be returned.SELECT count(cyclist_name) FROM cycling.country_flag WHERE country='Belgium';
Applying user-defined aggregate (UDA) functions
Using the SELECT command to return data and apply user-defined aggregate functions.
Referring back to the user-defined aggregate average(), retrieve the average of the column cyclist_time_sec from a table.
Procedure
-
List all the data in the table.
SELECT * FROM cycling.team_average;
-
Apply the user-defined aggregate function
average()
to the cyclist_time_sec column.SELECT average(cyclist_time_sec) FROM cycling.team_average WHERE team_name='UnitedHealthCare Pro Cycling Womens Team' AND race_title='Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe';