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';