MySQL using joins and queries to aggregate stats

Question

I have the following queries which work perfectly.

SELECT possession_team AS Team, ball_carrier_receiver AS Player,
    sum(run_pass='P') AS TAR,
    sum(pass_result='C') AS REC, 
    sum(gain) AS YDS,
    ROUND(sum(gain) / sum(pass_result='C'),1) as 'AVG COMP',
    max(gain) AS LG,
    sum(series_end='Touchdown' AND pass_result='C') AS TD,
    ROUND((sum(pass_result='C') / sum(run_pass='P')) * 100,1) AS 'COM%'
FROM plays 
WHERE run_pass='P' AND pass_result <>'S' AND pass_result <>'R'
GROUP BY possession_team, ball_carrier_receiver;

But I need to get the player's name matched to his stats so the output would be PLAYER NAME, TEAM, JERSEY followed by the sum of his statistics.

I have another table called game_rosters . This has first_name, last_name, team_code and jersey_number. Team_code = possession_team from the plays table, and jersey_number = ballcarrier_receiver_number in the plays table.

I tried a bunch of different statements and joins, but I can't figure it out. Below is the code I've come closest on but it just outputs the PLAYER NAME, TEAM and Jersey listed multiple times (but also lists players who haven't accumulated any of these stats) with no aggregation of any stats.

SELECT concat(fgr.last_name,',',' ',fgr.first_name) AS Player, possession_team AS Team, ball_carrier_receiver AS Jersey FROM football.game_rosters AS fgr
LEFT JOIN football.plays AS fplays ON (fgr.jersey_number = fplays.ball_carrier_receiver);
sum(run_pass='P') AS TAR,
sum(pass_result='C') AS REC, 
sum(gain) AS YDS,
ROUND(sum(gain) / sum(pass_result='C'),1) as 'AVG COMP',
max(gain) AS LG,
FROM fplays WHERE run_pass='P' AND pass_result <>'S' AND pass_result <>'R'
GROUP BY fplays.ball_carrier_receiver;

EDIT:

After reading up on Barmar's link below, I tried to do the subquery with sum played from game_rosters table. I can't get it to run, and I've made a mess of it I'm afraid.

SELECT ball_carrier_receiver AS Jersey, possession_team AS Team
FROM plays AS p
LEFT JOIN (
    SELECT CONCAT(last_name,', ',' ',first_name) AS Player, team_code AS TEAM,
           sum(played) AS GP
    FROM game_rosters AS gr
    GROUP BY team_code, jersey_number
) AS m
    ON m.jersey_number = p.ball_carrier_receiver AND p.possession_team = m.team_code
LEFT JOIN (
    SELECT ball_carrier_receiver AS Jersey, possession_team AS Team,
           sum(run_pass='P') AS TAR,
           sum(pass_result='C') AS REC, 
           sum(gain) AS YDS,
           ROUND(sum(gain) / sum(pass_result='C'),1) as 'AVG COMP',
           max(gain) AS LG,
           sum(series_end='Touchdown' AND pass_result='C') AS TD,
           ROUND((sum(pass_result='C') / sum(run_pass='P')) * 100,1) AS 'COM%'
    FROM plays
    WHERE run_pass='P' AND pass_result <>'S' AND pass_result <>'R' AND play_null='N'
    GROUP BY possession_team, ball_carrier_receiver ORDER BY TAR DESC
) as n
    ON m.jersey_number = n.ball_carrier_receiver AND n.possession_team = m.team_code;

What is your question? This is a mess. Ask 1 question & stick to it. You got an answer, if it is reasonable please edit your post to be what it should be for that answer. If you have another question, make another post. Don't change your question. If you get a result you don't expect, put your overall goal & its question on hold & find out what your misconception is, say what you expected & why, justified by authoritative documentation. For code questions give a [mre]. Don't add "EDIT"s, edit. Forget old versions. But don't edit in a way that invalidates reasonable posted answers. [ask] [Help] — philipxy, Aug 30 '21 at 06:04

score 0 · Accepted Answer · answered Aug 29 '21 at 03:33

0

Simply add a JOIN with the game_rosters after the FROM clause.

SELECT possession_team AS Team, CONCAT(first_name, ' ', last_name) AS Player, 
    sum(run_pass='P') AS TAR,
    sum(pass_result='C') AS REC, 
    sum(gain) AS YDS,
    ROUND(sum(gain) / sum(pass_result='C'),1) as 'AVG COMP',
    max(gain) AS LG,
    sum(series_end='Touchdown' AND pass_result='C') AS TD,
    ROUND((sum(pass_result='C') / sum(run_pass='P')) * 100,1) AS 'COM%'
FROM plays AS p
JOIN game_rosters AS gr 
    ON gr.jersey_number = p.ball_carrier_receiver AND p.possession_team = gr.team_code
WHERE run_pass='P' AND pass_result <>'S' AND pass_result <>'R'
GROUP BY possession_team, ball_carrier_receiver;

answered Aug 29 '21 at 03:33

Barmar

741,623
53
500
612

This works great, the exact output that I was looking for. – newcoderfball Aug 29 '21 at 03:51
If I wanted to add another column from the game_rosters table, how would I do that? Game_rosters has a "played" column. If I wanted to sum that column is it possible to add it to the above query? – newcoderfball Aug 29 '21 at 04:15
Of course. When you join tables, you can show columns from either table. This is basic to joins. – Barmar Aug 29 '21 at 04:53
I'd like to be able to have the sum of played as the first column after the PLAYER and TEAM. I tried the following, but it doesn't give the proper calculation. sum(gr.played) AS GP – newcoderfball Aug 29 '21 at 04:57
Are there multiple rows in `game_rosters` for the same player? You're grouping by `ball_carrier_receiver`, so anything you sum will be for just a single player. – Barmar Aug 29 '21 at 05:04
See https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049 regarding summing from different tables in a join. – Barmar Aug 29 '21 at 05:05
There can be multiple rows for the same player, each with a separate game_id. So as the season goes on they will have multiple games played, which is the number I am trying to get. I should add that played is either '1' or '0'. – newcoderfball Aug 29 '21 at 05:08
You need to join with a subquery that aggregates all the rows for a particular player. Otherwise, all your other sums will be multiplied by the number of games they played. See the above linked question. – Barmar Aug 29 '21 at 05:10
I read up on that link, tried to apply it to my problem. But I can't even get the code to run. See the edit to my question. – newcoderfball Aug 29 '21 at 05:45
You need to get the basic syntax right: `LEFT JOIN (SELECT ...)` – Barmar Aug 29 '21 at 06:09
I made some changes, reposted it to the edit. I still can't get it to run. It's giving me an error for "Unknown column 'last_name' in field list.". I tried adding a gr. in front of last_name, that didn't help. – newcoderfball Aug 29 '21 at 06:20
Also getting an error for 'Unknown column 'gr.jersey_number' in 'on_clause'. But I can't figure out why, as that is what we used in the original code. I'll have to check in on this again tomorrow. – newcoderfball Aug 29 '21 at 06:28
Why is the `ON` clause at the end the same as the earlier one. It should be relating columns in one of the first two tables with columns in `n` – Barmar Aug 29 '21 at 06:30
The outer query can't refer to tables inside a subquery. It can only refer to the columns returned by the subquery. – Barmar Aug 29 '21 at 06:31
I am lost. I am a total newbie to this stuff. – newcoderfball Aug 30 '21 at 03:38
I don't understand which is the subquery (the first one?). or the outer query (the second one?). And I really don't understand what columns I'm supposed to be relating. How does m and n fit in to this? Changing prefix gr to m doesn't give any response either. – newcoderfball Aug 30 '21 at 03:43
A subquery is a query nested inside another query. – Barmar Aug 30 '21 at 03:55
You say the second ON clause can't be same as first. Well what should it be? Those are the columns that are related to each other. There isn't anything else to relate. – newcoderfball Aug 30 '21 at 05:52
I've made some changes and posted to the EDIT. It doesn't work. This is really frustrating at this point, because I'm just going in circles. I need some specifics please for what to replace, and with what. – newcoderfball Aug 30 '21 at 06:05

MySQL using joins and queries to aggregate stats

1 Answers1