Sum of multiple rows in sql with join statement-CodePudding

I am trying to sum multiple rows of the same athlete so it will return the total amount of medals that they have won overall. I have the following code:

CREATE TABLE athlete (
    athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name TINYTEXT NOT NULL,
    country TINYTEXT NOT NULL,
    birthdate DATE NOT NULL,
    age INT UNSIGNED,
    height_inch INT UNSIGNED,
    PRIMARY KEY (athlete_id)
);

INSERT INTO athlete (name, country, birthdate, age, height_inch) VALUES ('Simone Biles', 'United States', '1997-03-14', 24, 56);
INSERT INTO athlete (name, country, birthdate, age, height_inch) VALUES ('Michael Phelps', 'United States', '1985-06-30', 36, 76);

CREATE TABLE sport (
    sport_id INT UNSIGNED NOT NULL,
    sport TINYTEXT NOT NULL,
    PRIMARY KEY (sport_id)
);

INSERT INTO sport VALUES (101, 'Skiing');
INSERT INTO sport VALUES (102, 'Biathlon');
INSERT INTO sport VALUES (103, 'Curling');
INSERT INTO sport VALUES (104, 'Skating');
INSERT INTO sport VALUES (105, 'Ice Hockey');
INSERT INTO sport VALUES (106, 'Luge');
INSERT INTO sport VALUES (107, 'Snowboard');
INSERT INTO sport VALUES (108, 'Basketball');
INSERT INTO sport VALUES (109, 'Gymnastics');
INSERT INTO sport VALUES (110, 'Swimming');
INSERT INTO sport VALUES (111, 'Diving');
INSERT INTO sport VALUES (112, 'Track and Field');
INSERT INTO sport VALUES (113, 'Badminton');
INSERT INTO sport VALUES (114, 'Tennis');
INSERT INTO sport VALUES (115, 'Volleyball');
INSERT INTO sport VALUES (116, 'Skateboard');
INSERT INTO sport VALUES (117, 'Soccer');
INSERT INTO sport VALUES (118, 'Golf');
INSERT INTO sport VALUES (119, 'Cycling');
INSERT INTO sport VALUES (120, 'Climbing');
INSERT INTO sport VALUES (121, 'Surfing');
INSERT INTO sport VALUES (122, 'Water Polo');
INSERT INTO sport VALUES (123, 'Karate');


CREATE TABLE olympics (
    olympics_id INT UNSIGNED NOT NULL,
    season TINYTEXT NOT NULL,
    year YEAR NOT NULL,
    city TINYTEXT NOT NULL,
    PRIMARY KEY (olympics_id)
);  

INSERT INTO olympics VALUES (1001, 'Summer', 1936, 'Berlin');
INSERT INTO olympics VALUES (1002, 'Summer', 1956, 'Melbourne');
INSERT INTO olympics VALUES (1003, 'Summer', 1960, 'Rome');
INSERT INTO olympics VALUES (1004, 'Summer', 1964, 'Tokyo');
INSERT INTO olympics VALUES (1005, 'Summer', 1976, 'Montreal');
INSERT INTO olympics VALUES (1006, 'Summer', 1984, 'Los Angelos');
INSERT INTO olympics VALUES (1007, 'Summer', 1996, 'Atlanta');
INSERT INTO olympics VALUES (1008, 'Summer', 2000, 'Sydney');
INSERT INTO olympics VALUES (1009, 'Summer', 2004, 'Athens');
INSERT INTO olympics VALUES (1010, 'Summer', 2008, 'Beijing');
INSERT INTO olympics VALUES (1011, 'Summer', 2012, 'London');
INSERT INTO olympics VALUES (1012, 'Summer', 2016, 'Rio de Janeiro');
INSERT INTO olympics VALUES (1013, 'Summer', 2020, 'Tokyo');

CREATE TABLE sport_events (
    sport_id INT UNSIGNED NOT NULL, 
    event_id INT UNSIGNED NOT NULL,
    event TINYTEXT NOT NULL,
    PRIMARY KEY (event_id),
    FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
);

INSERT INTO sport_events VALUES (101, 501, 'Alpine Skiing');
INSERT INTO sport_events VALUES (101, 502, 'Cross-Country Skiing');
INSERT INTO sport_events VALUES (104, 503, 'Figure Skating');
INSERT INTO sport_events VALUES (101, 504, 'Freestyle Skiing');
INSERT INTO sport_events VALUES (104, 505, 'Short Track Speed Skating');
INSERT INTO sport_events VALUES (101, 506, 'Ski Jumping');
INSERT INTO sport_events VALUES (107, 507, 'Half-pipe');
INSERT INTO sport_events VALUES (101, 508, 'Half-pipe');
INSERT INTO sport_events VALUES (104, 509, 'Speed Skating');
INSERT INTO sport_events VALUES (109, 510, 'Artistic Gymnastics');
INSERT INTO sport_events VALUES (109, 511, 'Rhythmic Gymnastics');
INSERT INTO sport_events VALUES (115, 512, 'Beach Volleyball');
INSERT INTO sport_events VALUES (112, 513, 'High Jump');
INSERT INTO sport_events VALUES (112, 514, '100m');
INSERT INTO sport_events VALUES (112, 515, '200m');
INSERT INTO sport_events VALUES (112, 516, '400m');
INSERT INTO sport_events VALUES (112, 517, '800m');
INSERT INTO sport_events VALUES (112, 518, '4x100m relay');
INSERT INTO sport_events VALUES (112, 519, 'Triple Jump');

CREATE TABLE athlete_sport (
    athlete_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sport_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (athlete_id),
    FOREIGN KEY (athlete_id) REFERENCES athlete (athlete_id),
    FOREIGN KEY (sport_id) REFERENCES sport (sport_id)
);

INSERT INTO athlete_sport (sport_id) VALUES (109);
INSERT INTO athlete_sport (sport_id) VALUES (110);

CREATE TABLE compete (
    athlete_id INT UNSIGNED NOT NULL,
    olympics_id INT UNSIGNED NOT NULL,
    sport_id INT UNSIGNED NOT NULL,
    event_id INT UNSIGNED,
    gold INT UNSIGNED,
    silver INT UNSIGNED,
    bronze INT UNSIGNED,
    FOREIGN KEY (olympics_id) REFERENCES olympics (olympics_id),
    FOREIGN KEY (athlete_id) REFERENCES athlete (athlete_id),
    FOREIGN KEY (event_id) REFERENCES sport_events (event_id)   
);

INSERT INTO compete VALUES (1, 1012, 109, 510, 4, 0, 1);
INSERT INTO compete VALUES (1, 1013, 109, 510, 0, 1, 1); 
INSERT INTO compete VALUES (2, 1009, 110, NULL, 6, 0, 2);
INSERT INTO compete VALUES (2, 1010, 110, NULL, 8, 0, 0);
INSERT INTO compete VALUES (2, 1011, 110, NULL, 4, 2, 0);
INSERT INTO compete VALUES (2, 1012, 110, NULL, 5, 1, 0);

I have looked at other answers people have posted and most of them are just saying to use group by, but when I use that it just orders the either names or number of medals together just in a different order. I'm trying to get it so it says that the total number of medals simone biles has is 7 and michael phelps is 28 just in a single table.

This is the query I have that returns the sum of the medals for each olympic games they've been in, but again if I use group by it just orders them.

 select a.name, gold silver bronze as medalTotal from athlete a join compete c using (athlete_id) group by medalTotal;
 ---------------- ------------ 
| name           | medalTotal |
 ---------------- ------------ 
| Simone Biles   |          2 |
| Simone Biles   |          5 |
| Michael Phelps |          6 |
| Michael Phelps |          8 |
 ---------------- ------------

CodePudding user response：

you need to do the following:

group by athlete
sum each medal type
add the sum of each medal type

CodePudding user response：

You need to group by athlete, not total.

You have to use an aggregation function to combine values from all the rows in a group. Use SUM() to add them together.

select a.name, SUM(b.gold b.silver b.bronze) as medalTotal 
from athlete a 
join compete c using (athlete_id) 
group by a.athlete_id;

CodePudding user response：

You should apply group by using something unique like athelet id.

This get all atheletes with all medals number including with zero medals if you want just athelet with medals only use join only.

select 
   a.name, sum(gold silver bronze) as medalTotal 
from 
   athlete a 
left join 
   compete c on c.athlete_id = a.athlete_id 
group by 
   a.athlete_id