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;

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com