team_average
Partition key with two clustering columns. Shows use of aggregate and functions.
SOURCE 'keyspace-create-simple.cql'; SOURCE 'keyspace-use.cql'; // tag::table-drop-team-average[] DROP TABLE IF EXISTS cycling.team_average; // end::table-drop-team-average[] // tag::table-create-team-average[] CREATE TABLE IF NOT EXISTS cycling.team_average ( team_name text, cyclist_name text, cyclist_time_sec int, race_title text, PRIMARY KEY (team_name, race_title, cyclist_name) ); // end::table-create-team-average[] // tag::data-insert-team-average[] INSERT INTO cycling.team_average (team_name, cyclist_name, cyclist_time_sec, race_title) VALUES ('UnitedHealthCare Pro Cycling Womens Team','Katie HALL',11449,'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'); INSERT INTO cycling.team_average (team_name, cyclist_name, cyclist_time_sec, race_title) VALUES ('UnitedHealthCare Pro Cycling Womens Team','Linda VILLUMSEN',11485,'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'); INSERT INTO cycling.team_average (team_name, cyclist_name, cyclist_time_sec, race_title) VALUES ('UnitedHealthCare Pro Cycling Womens Team','Hannah BARNES',11490,'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'); INSERT INTO cycling.team_average (team_name, cyclist_name, cyclist_time_sec, race_title) VALUES ('Velocio-SRAM','Alena AMIALIUSIK',11451,'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'); INSERT INTO cycling.team_average (team_name, cyclist_name, cyclist_time_sec, race_title) VALUES ('Velocio-SRAM','Trixi WORRACK',11453,'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'); INSERT INTO cycling.team_average (team_name, cyclist_name, cyclist_time_sec, race_title) VALUES ('TWENTY16 presented by Sho-Air','Lauren KOMANSKI',11451,'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe'); // end::data-insert-team-average[] // tag::query-team-average[] SELECT cycling.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'; // end::query-team-average[] CAPTURE 'select_average_from_team_average.results'; // tag::AVG[] SELECT AVG(cyclist_time_sec) AS Average FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team'; // end::AVG[] CAPTURE OFF; CAPTURE 'select_count_from_team_average.results'; // tag::COUNT[] SELECT COUNT(cyclist_name) AS Row_Count FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team'; // end::COUNT[] CAPTURE OFF; CAPTURE 'select_min_from_team_average.results'; // tag::MIN[] SELECT MIN(cyclist_time_sec) AS Fastest FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team'; // end::MIN[] CAPTURE OFF; CAPTURE 'select_max_from_team_average.results'; // tag::MAX[] SELECT MAX(cyclist_time_sec) AS Slowest FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team'; // end::MAX[] CAPTURE OFF; CAPTURE 'select_sum_from_team_average.results'; // tag::SUM[] SELECT SUM(cyclist_time_sec) AS Total_Time FROM cycling.team_average WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team'; // end::SUM[] CAPTURE OFF;