CODEWITHSUNDEEP
sqlderby
MCR
MCR

Reputation: 1643

Trouble With GROUP BY Learning Derby SQL

I have formed this query to produce the pitcher from each team who has the most wins. My trouble comes in that I need to group them a certain way and I keep having scoping issues when trying to do so. W is the number of wins for each pitcher. Here is my pre-grouped statement...

SELECT 
    (SELECT p1.nameFirst FROM Players p1 Where (one.playerID = p1.playerID)), 
    (SELECT p1.nameLast FROM Players p1 Where (one.playerID = p1.playerID)), 
    one.W, (SELECT t1.name FROM Teams t1 Where(one.teamID = t1.teamID)) 
 FROM Pitching one
 Where (one.W >= ALL
    (SELECT two.W
    FROM Pitching two
    Where (two.teamID = one.teamID)));

I need to group the tuples by league and within the leagues group by division. League (lgID) and division (divID) exist in the Teams table. Can someone point me in the right direction? Thank you.

This is top six rows of what is currently output...

Zach    Britton         11  Baltimore Orioles
Mark    Buehrle         13  Chicago White Sox
Madison Bumgarner       13    San Francisco Giants
Jhoulys Chacin          11  Colorado Rockies
Bruce   Chen            12  Kansas City Royals
Kevin   Correia         12  Pittsburgh Pirates

My desired output is to have these teams sorted by league (NL/AL) and within the leagues have them sorted by division.

Upvotes: 0

Views: 531

Answers (2)

swasheck
swasheck

Reputation: 4693

This may be an issue of structure. Using what I believe to be your structure we could probably narrow it down like this:

 select Players.nameFirst, Players.nameLast, TopPitcher.Winner, 
  Teams.name, League.Name, Division.Name
    from    (select playerID, max(Wins) as Winner
    from (select playerID, teamID, count(W) as Wins
        from Pitching
        group by playerID, teamID ) PitchingWins) TopPitcher       
    join Players 
    on TopPitcher.playerID = Players.playerID 
    join Teams 
    on Teams.teamID = Players.teamID
    join League 
    on Teams.leagueID = League.leagueID
    join Division 
    on League.divisionID = Division.divisionID
    order by League.Name, Division.Name

Now. Having said that, this is only for the structure you've given (with some other interpolation). Your overall structure is faulty as I would probably relate Player to Teams and not Pitching to Teams since you might get some sort of data errors regarding team wins vs. pitcher wins.

Upvotes: 1

Michael Rice
Michael Rice

Reputation: 1183

Based on your updated comment. I think this is what you want.

SELECT
   pl.nameFirst
   , pl.nameLast
   , p.W
   , t.name
FROM
(
    SELECT
        MAX(p1.W) AS W
        , p1.teamId
    FROM
    Pitching p1
    GROUP BY
        p1.teamId
) t1
JOIN
    Pitching p
       ON t1.W = p.W
       AND t1.teamId = p.teamId
JOIN
   Players pl
     ON p.playerID = pl.playerID
JOIN
   Teams t
     ON p.teamID = t.teamID
ORDER BY
   t.lgID
   , t.divID

I do agree with swasheck, there are some opportunities for improvement in your schema. As swascheck said, teamId should be in Players. Not in Pitching.

Upvotes: 1

Related Questions