CODEWITHSUNDEEP
mysqlsql
Abraham Lincoln
Abraham Lincoln

Reputation: 43

SQL Query to return maximums over decades

THIS IS USING MYSQL My question is the following. I have a baseball database, and in that baseball database there is a master table which lists every player that has ever played. There is also a batting table, which tracks every players' batting statistics. I created a view to join those two together; hence the masterplusbatting table. I now want to find the highest HR hitter in each decade since baseball began. Here is what I tried.

    select f.yearID, truncate(f.yearid/10,0) as decade,f.nameFirst, f.nameLast, f.HR
    from (
    select yearID, max(HR) as HOMERS
    from masterplusbatting group by yearID
    )as x inner join masterplusbatting as f on f.yearID = x.yearId and f.HR = x.HOMERS
    group by decade

You can see that I truncated the yearID in order to get 187, 188, 189 etc instead of 1897, 1885,. I then grouped by the decade, thinking that it would give me the highest per decade, but it is not returning the correct values. For example, it's giving me Adrian Beltre with 48 HR's in 2004 but everyone knows that Barry Bonds hit 73 HR in 2001. Can anyone give me some pointers?

Upvotes: 4

Views: 1773

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

Your query seems to be aggregating by the wrong thing. Somewhere, I expect to see a group by based on the player.

select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs
from masterplusbatting mpb
group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast

This gives the total number of home runs in each decade. Now, you want the person with the most. For this, I would use the row_number() function and subqueries:

select decade, nameFirst, nameLast, HRs
from (select decade, nameFirst, nameLast, HRs,
             row_number() over (partition by decade order by HRs desc) as seqnum
      from (select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs
            from masterplusbatting mpb
            group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast
           ) t
     ) t
where seqnum = 1
order by 1

In a database such as MySQL, you need to do this with a second join:

select t.*
from (select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs
      from masterplusbatting mpb
      group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast
     ) t join
     (select decade, max(HRs) as maxHRs
      from (select truncate(mpb.yearid/10, 0) as decade, mpb.nameFirst, mpb.nameLast, sum(mpb.HR) as HRs
            from masterplusbatting mpb
            group by truncate(mpb.yearid/10, 0), mpb.nameFirst, mpb.nameLast
           ) t
      group by decade
     ) tsum
     on t.decade = tsum.decade and t.HRs = tsum.maxHRs
order by decade

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

SELECT
  Lookup.DecadeID,
  Data.*
FROM
(
  SELECT
    truncate(yearid/10,0) as decadeID,
    MAX(HR) as Homers
  FROM
    masterplusbatting
  GROUP BY
    truncate(yearid/10,0)
)
  AS lookup
INNER JOIN
  masterplusbatting AS data
    ON  data.yearid >= lookup.decadeID * 10
    AND data.yearid <  lookup.decadeID * 10 + 10
    AND data.HR     =  lookup.homers

Editted for MySQL

Upvotes: 2

Related Questions