CODEWITHSUNDEEP
mysqlsorting
don't blink
don't blink

Reputation: 101

How to sort teams by summarized score in SQL? (two columns sorting with SUM())

Playerbase have 3 columns: PlrName, TeamTag, Score I need to sort players grouping them by TeamTag. And put teams with best summarized score above.

So want to figure out what query will help make this:

PlrName|TeamTag|Score        PlrName|TeamTag|Score
-------------------------------------------------------
Player1|TeamThr|0            Player6|TeamThr|9  \
Player2|TeamTwo|2            Player1|TeamThr|0   > 9
Player3|TeamOne|4            Player4|TeamThr|0  /
Player4|TeamThr|0            Player5|TeamOne|4  \
Player5|TeamOne|4  became->  Player3|TeamOne|4   > 8
Player6|TeamThr|9            Player8|TeamOne|0  /
Player7|TeamTwo|2            Player2|TeamTwo|2  \
Player8|TeamOne|0            Player7|TeamTwo|2   > 6
Player9|TeamTwo|2            Player9|TeamTwo|2  /

added: With this query i can get an array(?) of TeamTag's ordered by team score:

SELECT TeamTag FROM Playerbase GROUP BY team ORDER BY SUM(Score) DESC

..can I sort then PlayerBase using this "array"? Prefably within one query :) Also i need to get full lines (using *), not only three fields.

Upvotes: 0

Views: 84

Answers (4)

don't blink
don't blink

Reputation: 101

I made it like this, but feel little uncomfortable about sql :D

    SELECT * FROM `Playerbase` as T1
    JOIN
    (SELECT TeamTag, SUM(Score) AS Sum FROM `Playerbase` GROUP BY TeamTag ORDER BY Sum DESC) AS T2
    ON T2.TeamTag = T1.TeamTag

Didn't make what i was trying to, but anyway thanks for answers. They helped me to understand a bit more :D

Upvotes: 0

FanoFN
FanoFN

Reputation: 7114

If your dbms doesn't support window functions (any MySQL version below 8.0):

SELECT PlrName, TeamTag, Score FROM table a
LEFT JOIN 
(SELECT PlrName, TeamTag, Score ,SUM(score) ts FROM table GROUP BY TeamTag) b 
ON a.TeamTag=b.TeamTag
ORDER BY b.ts DESC, a.TeamTag, score DESC;

Upvotes: 1

Serg
Serg

Reputation: 22811

Assuming your dbms supports window functions

select PlrName, TeamTag, Score
from (
    select PlrName, TeamTag, Score, sum(Score) over (partition by TeamTag) ms
    from tablename ) t
order by ms desc, TeamTag, Score desc

EDIT changed max() to sum() as OP refined the problem explanation.

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

Try this

; with cte as (
select  TeamTag, sum(Score) as Sum
from tablename)
select t.PlrName, t.teamtag, t.Score from tablename as t inner join cte as c 
on t.teamtag=c.teamtag order by c.sum desc, t.score desc

Upvotes: 0

Related Questions