CODEWITHSUNDEEP
mysql
Christian Åvall
Christian Åvall

Reputation: 45

SQL Sum top 5 rows for each team

I got help with this SQL-question below to sum up top 5 lenght for each team. The problem for me now seems that SQL is randomly selecting 5 of the for eg 7 rows in the database for team 25, not the top 5. Anyone has any ideas for me? Would be much appriciated!

select team, sum(length) as totalScore 
from 
(
    SELECT
        t.*,
        @num_in_group:=case when @team!=team then @num_in_group:=0 else @num_in_group:=@num_in_group+1 end as num_in_group,
        @team:=team as t
    FROM reg_catches t, (select @team:=-1, @num_in_group:=0) init
    ORDER BY team asc
) sub
WHERE
    sub.num_in_group <= 4 and
    competition = 16    and
    team = 25 
GROUP BY team
ORDER BY totalScore DESC;

Table

team length competition
----------------------
26   70       16
25   70       16
25   95       16
25   98       16
25   100      16
25   100      16
25   100      16
25   122      16

Wanted output:

team totalScore
---- -----------
25  520
26  70

Best regards, Christian

Upvotes: 3

Views: 792

Answers (3)

Chris J
Chris J

Reputation: 1447

If you wanted to get the top 5 rows for each team, you could utilise a parameter to rank each team's results:

SELECT *, 
(@rank := if(@team = team, @rank + 1, if(@team := team, 1, 1))) as rank 
FROM (SELECT * from reg_catches order by team, length desc) ordered
HAVING rank <= 5
ORDER BY team, rank asc

Which would produce the following: http://sqlfiddle.com/#!9/6042ae/1

| team | length | competition | rank |
+------+--------+-------------+------+
|  25  |   99   |     16      |   1  |
|  25  |   98   |     16      |   2  |
|  25  |   77   |     16      |   3  |
|  25  |   76   |     16      |   4  |
|  25  |   73   |     16      |   5  |
|  26  |   96   |     16      |   1  |
|  26  |   88   |     16      |   2  |
|  26  |   87   |     16      |   3  |
|  26  |   83   |     16      |   4  |
|  26  |   79   |     16      |   5  |

You could then, if required, wrap that query in a simple aggregation/group to get the total of the top 5 for each team

SELECT team, sum(length) as 'total score' FROM
(SELECT *, 
(@rank := if(@team = team, @rank + 1, if(@team := team, 1, 1))) as rank 
FROM (SELECT * from reg_catches order by team, length desc) ordered
HAVING rank <= 5
ORDER BY team, rank asc) top5
GROUP BY team

As per http://sqlfiddle.com/#!9/6042ae/2

| team  |total score|
+-------+-----------+
|  25   |   423     |
|  26   |   433     |
|  27   |   426     |

The usual caveats will apply to parameters, but you can't really 'rank' in MySQL without them. I also found this very sensitive to initially getting the scores in the right order before applying the parameters.

Whilst this might not be the most polished solution, I hope it helps

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Besides any logical problems you have with your current query, there may be other risks in the form of the behavior of MySQL session variables. MySQL does not guarantee the order in which assignments to session variables will take place in a select statement. Hence, a query to simulate rank with a partition (your problem) may not be reliable. But we can still solve the problem by using a correlated subquery to label the top 5 lengths for each team:

SELECT
    team,
    SUM(length) AS totalScore
FROM
(
    SELECT
        team,
        length,
        competition,
        (SELECT COUNT(*) FROM reg_catches t2
         WHERE t2.team = t1.team AND t2.length >= t1.length) cnt
    FROM reg_catches t1
) t
WHERE
    cnt <= 5 AND
    competition = 16
GROUP BY
    team
ORDER BY
    team;

Note that this query has a limitation should there be ties present. If you expect ties then we could work around this by using some other column to break the tie.

Output:

    team    totalScore
1   25      520
2   26      70

Demo here:

Rextester

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

In your inner query add another ordering clause as 

 ORDER BY team asc ,length desc

DEMO

This will order rows of table first with team and then for each team it will order rows by length column value, so higher value for team will be returned first

Or another way to get you the same results without using variables 

select a.team, sum(a.length) as totalScore 
from (
select b.*,(
    select count(*) 
    from reg_catches c
    where b.team = c.team
    and b.length < c.length
   ) + 1 rownum
from reg_catches b
) a
where a.rownum <=5
group by a.team
order by totalScore DESC;

DEMO

Make sure to have an index on team and length column to get better performance

Upvotes: 1

Related Questions