merge two columns, average with group by and find minimum with group by

Question

The title probably doesn't accurately describe my question, sorry about that.

I have two tables:

    2016Athletes:
    Name                  Position    40_dash   Year   Overall
    Jared Goff              QB         4.82     2016      1
    Carson Wentz            QB         4.77     2016      2
    Joey Bosa               DE         4.88     2016      3
    Ezekiel Elliott         RB         4.47     2016      4
    Jalen Ramsey            CB         4.41     2016      5
    ............

and

    2017Athletes:
    Name                 Position    40_dash    Year   Overall
    Myles Garrett           DE        4.64      2017      1
    Mitchell Trubisky       QB        4.67      2017      2
    Solomon Thomas          DE        4.69      2017      3
    Leonard Fournette       RB        4.51      2017      4
    Corey Davis             WR        NULL      2017      5
    ..............

I first want to merge the 40_dash columns and find the average, which I could do a couple ways, but the real problem I'm having is that after I find the average, I then want to group by position. I'm not sure it it's relevant, but the goal is to eventually select one particular athlete I know to be an impressive outlier and compare him to the average for all athletes, all defensive players, and then all linebackers and this grouping is my initial baseline for the following queries in my report.

My best crack at it would be

    select avg(40_dash)
    from ((select 40_dash from 2016Athletes) union ALL
          (select 40_dash from 2017Athletes) 
          )sl GROUP BY Position;

However, I know that won't work because the group by function is not correct. I'm not sure how to called the 'unioned' column once I have them merged.

Afterwards, I want to find each athlete with the fasted 40_dash time, also grouped by position. A min() function would seem to do the job, but again the group by is what's tripping me up.

Thanks to anyone who can assist!

Are these all the columns? – Strawberry Apr 28 '18 at 03:20 — Strawberry, Apr 28 '18 at 03:20

Barmar · Accepted Answer · 2018-04-28T00:42:09.687

2

You need to select the position column in the subquery, so you can group by it in the main query.

SELECT Position, AVG(40_dash)
FROM (
    SELECT Position, 40_dash FROM 2016Athletes
    UNION ALL
    SELECT Position, 40_dash FROM 2017Athletes
) as x
GROUP BY Position

See SQL select only rows with max value on a column for how to find the player with the fastest time in each group. You can then join that query with the above query, to compare the fastest to the average.

edited Apr 28 '18 at 00:42

answered Apr 28 '18 at 00:23

Barmar

741,623
53
500
612

Wow, I can do it all in one query? I'll take a look at that link right now! additionally, this query only seems to pull the average 40_dash time for QB, not all positions. I'll keep playing with it to see if I can figure it out for all positions and then add the grouping function. edit: figured out the grouping issue, very simple fix. Thank you! – Nick Bohl Apr 28 '18 at 00:40
I forgot GROUP BY – Barmar Apr 28 '18 at 00:42

merge two columns, average with group by and find minimum with group by

1 Answers1