Help with SQL Queries

• tempdownloads

  SSC Enthusiast

  Points: 189

  More actions

  October 25, 2018 at 9:51 pm

  #2011281

  Can anyone help with this question please?:

  Create a table to show the aggregate workout hours for each “Hard” and “Basic” level of workout endurance by day:
  

  
CREATE TABLE athlete_table (athleteID VARCHAR(3), endurance_group VARCHAR(5));
INSERT INTO athlete_table VALUES (‘201’,’Hard');
INSERT INTO athlete_table VALUES (‘202’,’Basic');
INSERT INTO athlete_table VALUES (‘203’,’Hard');


  
CREATE TABLE workout_table (date_ VARCHAR(4), athleteID VARCHAR(3), Workout_Hours INT);
INSERT INTO workout_table VALUES ('12/3','201','54');
INSERT INTO workout_table VALUES ('12/4','203','19');
INSERT INTO workout_table VALUES ('12/7','201','32');

  
CREATE TABLE track_table (date_ VARCHAR(4), athleteID VARCHAR(3));
INSERT INTO track_table VALUES ('12/1','201');
INSERT INTO track_table VALUES ('12/3','201');
INSERT INTO track_table VALUES ('12/3','202');
INSERT INTO track_table VALUES ('12/4','201');
INSERT INTO track_table VALUES ('12/4','203');
INSERT INTO track_table VALUES ('12/5','202');

  
CREATE TABLE date_table (date_ VARCHAR(4));
INSERT INTO date_table VALUES (’12/1’);
INSERT INTO date_table VALUES (’12/2’);
INSERT INTO date_table VALUES (’12/3’);
INSERT INTO date_table VALUES (’12/4’);
INSERT INTO date_table VALUES (’12/5’);
INSERT INTO date_table VALUES (’12/6’);

  Here's what my query:

  
SELECT d.date_, SUM(w.workout_hours) AS ‘Total_Workout_High’, SUM(w.workout_hours) AS ‘Total_Workout_Low’
FROM workout_table w
JOIN
athlete_table a ON w.athleteID = a.athleteID
JOIN
date_table d ON w.date_ = d.date_
WHERE a.Endurance_Group = 'Hard', a.Endurance_Group = 'Basic'
GROUP BY d.date_;

• tempdownloads

  SSC Enthusiast

  Points: 189

  More actions

  October 25, 2018 at 10:21 pm

  #2011283

  I think i got it. 

  Can someone please double check?

  
SELECT d.date_, SUM(w.workout_hours) AS Total_Workout_High, SUM(w.workout_hours) AS Total_Workout_Low
    FROM workout_table w
JOIN
athlete_table a ON w.athleteID = a.athleteID
JOIN
date_table d ON w.date_ = d.date_
    WHERE
    a.endurance_group = 'Hard' OR
    a.endurance_group = 'Basic'
    GROUP BY d.date_;


• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 26, 2018 at 2:42 am

  #2011305

  tempdownloads - Thursday, October 25, 2018 10:21 PM

  I think i got it. 

  Can someone please double check?

  
SELECT d.date_, SUM(w.workout_hours) AS Total_Workout_High, SUM(w.workout_hours) AS Total_Workout_Low
    FROM workout_table w
JOIN
athlete_table a ON w.athleteID = a.athleteID
JOIN
date_table d ON w.date_ = d.date_
    WHERE
    a.endurance_group = 'Hard' OR
    a.endurance_group = 'Basic'
    GROUP BY d.date_;


  Data for 12/7 (these should still be dates) appears to be missing; I assume it should be included? Should you also be providing data for day where there are no work outs?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk