Results 1 to 13 of 13

Thread: SUM on TOP 3

  1. #1
    Join Date
    May 2004
    Posts
    19

    Post Unanswered: SUM on TOP 3

    Hi,

    SQL 2000 problem

    I have a group of players with a finish position which I need to find the top player group from, this is based on the lowest total of the top 3 finishers for a group.

    To qualify as a winner the player group must have 3 or more members, players can enter under a group name with two or even 1 member, but they cannot win the team event.

    Table structure is simple it contains player groupname and the score/finishing position for the individual finisher

    e.g.

    PlayerGroup1, 10
    PlayerGroup1, 15
    PlayerGroup1, 20
    PlayerGroup1, 35
    PlayerGroup1, 57
    PlayerGroup2, 3
    PlayerGroup2, 16
    PlayerGroup2, 17
    PlayerGroup2, 45
    PlayerGroup 3, 2
    PlayerGroup4, 1
    PlayerGroup4, 4

    PlayerGroup3 are eliminated by virtue of only having 1 member, the same for PlayerGroup 4 who only have 2 members.

    I now need to find the sum of the top 3 scores for PlayerGroup1 and PlayerGroup2 with the lowest sum being the winning team.

    Here is my SQL so far which brings back all the teams with more than 2 members, but the sum is for all entries and not just the top 3 for each group.

    SELECT PlayerGroup, SUM(RacePosition) AS Total
    FROM ClubEntries
    GROUP BY PlayerGroup, RacePosition HAVING COUNT(ClubName) > 2
    ORDER BY Total

    How do I factor into this query to only count or sum up the top 3 finishers for each club?

    Any help here would be most helpful.

    Thanks.
    Last edited by g2bam; 06-02-09 at 13:04.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Groups with at least 3 members:
    Code:
    SELECT group_name
    FROM   groups
    GROUP
        BY group_name
    HAVING Count(*) >= 3
    Join this derivation back to your members table to retrieve the scores for members in eligible groups.
    Code:
    SELECT g.group_name
         , s.score
    FROM   scores As s
     INNER
      JOIN (
            SELECT group_name
            FROM   groups
            GROUP
                BY group_name
            HAVING Count(*) >= 3
           ) As g
        ON s.group_name = g.group_name
    Use the Row_Number() windowed function to identify the top 3 scores per group
    Code:
    SELECT group_name
         , Sum(score)
    FROM   (
            SELECT g.group_name
                 , s.score
                 , Row_Number() OVER (PARTITION BY g.group_name ORDER BY s.score DESC) As row_num
            FROM   scores As s
             INNER
              JOIN (
                    SELECT group_name
                    FROM   groups
                    GROUP
                        BY group_name
                    HAVING Count(*) >= 3
                   ) As g
                ON s.group_name = g.group_name
           ) As an_alias
    WHERE  row_num <= 3
    Completely untested but should work
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Posts
    19
    Thanks for this wonderful code, but unfortunately its not going to work on SQL 2000 with Row_Number() function.

    I should also state there is an IDENTITY column for all records too, commencing 1 BY 1, perhaps the ID for these can be used as we can't use Row_Number?

    Sorry for that omission in the first post I placed.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Try this:

    SELECT PlayerGroup, SUM(score) TotalScore
    FROM
    (
    SELECT t1.PlayerGroup, t1.score
    FROM tbl t1
    JOIN tbl t2
    ON t1.PlayerGroup = t2.PlayerGroup
    WHERE t1.score < t2.score
    OR (t1.score = t2.score
    AND t1.id <= t2.id)
    GROUP BY t1.PlayerGroup,t1.score, t1.id
    HAVING COUNT(*)<=3
    ) t
    GROUP BY PlayerGroup
    HAVING COUNT(*)=3
    ORDER BY TotalScore;

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    g2bam, Do you have a know, finite number of PlayerGroups? The only way I can see that this might be doable in just SQL is to hardcode the PlayerGroups into the query or do Joins/Subquerys of a set number. If you were using SQL Server 2005, there would be easier/better ways to accomplish this.

    Otherwise you will need a Stored Procedure (or possibly a table function) to do the looping required. The logic in the Stored Procedure/Function is pretty easy (Get a List and Count of the Distinct PlayerGroups with 3 or more people, Loop on the Count, and get the Top 3 for Each PlayerGroup as you go through the loop).

    You can try dportas's query but I just don't see it working. This is based on ID being the Identity column you mentioned. This number is 'random' in relation to the PlayerGroups so I can't see T1.ID <= T2.ID doing anything (but I could be wrong).

    For example, if the PlayerGroups were entered in no particular order, the data could be:

    1 PlayerGroup1 10
    2 PlayerGroup3 2
    3 PlayerGroup4 1
    4 PlayerGroup2 16
    5 PlayerGroup4 4

    Now, if the data was entered in a strict Sort Order (All PlayerGroup1 in order by Score, then PlayerGroup 2 in order by Score, etc.), then this could work. You would in effect have the ROW_NUMBER function that sequential numbers the rows in a set sort order as gvee suggested.

    Despite my reservations, I hope it does work for you.

  6. #6
    Join Date
    May 2004
    Posts
    19
    Hi Again,

    Thanks for all the input, I am glad to see this really is quite a stinker of a problem, I was getting worried about myself!

    dportas: - Thanks for your code, but like what has just been said, due to the way the ID's stack up, I get the 2nd to 4th records in some cases and not the top 3.

    Now I think I may be able somehow to decant the top 3 records for each club into a temp table and simply do my select from that where all records will be clubs with 3 members, already picked by top 3 and I can select the lowest sum from that.

    I was after a single SELECT for this job, always best and the most efficient way to do it, but temp tables do provide a little safety bridge at times.

    I will test that and see if I can get that set up to work and let everyone know how it goes.

    But if any of you guru's wish to continue helping with the code methods, please do so, after all my plan may not work.

    Thanks.

  7. #7
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    Is this what you are looking for?


    select
    a.playergroups,
    sum(b.rank)
    from
    players a
    left join
    (
    select
    a.Playergroups
    ,a.rank
    from players a
    inner join
    players b
    on
    a.playergroups = b.playergroups
    and
    a.rank <= b.rank
    group by
    a.playergroups
    ,a.rank
    having count(a.rank) <= 3
    ) b
    on
    a.playergroups = b.playergroups
    and a.rank=b.rank
    group by a.playergroups having count(a.rank)>2

    here is my quasitable (players)
    playergroups rank
    pgroup1 10
    pgroup1 15
    pgroup1 25
    pgroup1 30
    pgroup1 57
    pgroup2 3
    pgroup2 16
    pgroup2 17
    pgroup2 45
    pgroup3 2
    pgroup4 1
    pgroup4 4

    and this is the final result
    pgroup1 112
    pgroup2 78

  8. #8
    Join Date
    May 2004
    Posts
    19
    burkular: - The result I am after would be..

    pgroup2 36
    pgroup1 50

    With pgroup3 & 4 not included. The 'score' in this case is crossing a finish line, therefore the smaller score wins, for the winnng team this is the sum of the lowest 3 finishers, the lowest sum of the combined top 3 finishers is the winning team, but each team or group must have at least three members and only the lowest 3 scores get summed for each group.

    Thanks for the code, appreciate all the other approaches different devver's take here.

    This is a real poser of a query, which I never seen coming before I started coding it.

    Very interesting, even for experienced coders, which I regard myself as.

  9. #9
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    The only other thing i can think of off hand is creating a cursor for each of the groups that puts the records you want into a temp table with an identity (where the identity/seq) is less than 3 then moving that to another temp table and summing for each of the distinct groupings.

    ie.
    pgroup1 10 1
    pgroup1 15 2
    pgroup1 35 3
    pgroup1 40 4
    pgroup1 45 5
    pgroup2 3 1
    pgroup2 10 2
    pgroup2 15 3
    pgroup2 29 4
    pgroup2 35 5
    pgroup3 8 1
    pgroup3 10 2
    pgroup3 15 3
    pgroup3 20 4

    Then having a query that goes like
    select distinct pgroup, sum(rank)
    where seq < 3
    group by pgroup

    DECLARE myc CURSOR

    FOR select distinct a.playergroups
    from players a
    Group by a.playergroups

    having count(a.rank) >=3

    DECLARE @pgroup varchar(40)
    OPEN myc

    FETCH NEXT FROM myc INTO @pgroup
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    --
    drop table #tmp
    Create table #tmp (playergroups varchar(10), rank int, sorder int IDENTITY)
    Insert into #tmp
    select
    a.Playergroups
    ,a.rank
    from players a
    inner join
    players b
    on
    a.playergroups = b.playergroups
    where a.playergroups = @pgroup
    group by
    a.playergroups
    ,a.rank
    having count(a.rank) >=3

    Order by 1 asc, 2 asc


    END

    FETCH NEXT FROM myc INTO @pgroup

    Declare @tmp2 table (playergroups varchar(10), rank int)
    insert into @tmp2
    select playergroups, rank from #tmp where sorder<=3

    END

    CLOSE myc
    DEALLOCATE myc

    select * from @tmp2 where playergroups is not null
    Last edited by burkular; 06-03-09 at 02:40.

  10. #10
    Join Date
    May 2004
    Posts
    19
    Hi All,

    dportas: - Thanks for your efforts, I juggled some stuff around and removed the ID from the table and slightly adjusted your code, and I now have what I wanted.

    The code is below for your reference. Well done to you for this, a truly well thought out and knowledgeable approach.

    Thanks for everyone else's contributions, its marvellous to share things likem this with dedicated professional seasoned campaigners.

    SELECT TOP 1 ClubName, SUM(RacePosition) TotalScore
    FROM
    (
    SELECT t1.ClubName, t1.RacePosition
    FROM Clubs t1
    INNER JOIN Clubs t2
    ON t1.ClubName = t2.ClubName
    WHERE t1.RacePosition >= t2.RacePosition
    OR (t1.RacePosition = t2.RacePosition)
    GROUP BY t1.ClubName,t1.RacePosition
    HAVING COUNT(t1.ClubName)<=3
    ) t
    WHERE UPPER(ClubName) <> 'NONE'
    GROUP BY ClubName
    HAVING COUNT(*)=3
    ORDER BY TotalScore

    This thread can now be considered closed and the issue resolved.


  11. #11
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I don't think your query will work correctly if it's possible to have a draw for RacePosition. If that never happens then you are right that you don't need the Id as a tie-breaker.

  12. #12
    Join Date
    May 2004
    Posts
    19
    I understand that, but I have the historical records going back 8 years and because there are Males and Females and we're filtering on gender, there are huge gaps in the finish places between everyone, and each year so far the gap beteen the 1st club and the 2nd club has been in the hundreds for each set of results for each gender.

    There's a 'slight' risk of a tie on the 'sum' which I can cover off if it happens year on year. Running the query against the existing years has produced exactly the club results for the last 8 years which the client obtains manually.

    Happy days!

    Thanks for your wonderful input.

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    set rowcount 3
    query
    set rowcount 0

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •