Sort table by group with highest sum and displaying individual entries

Question

I have following scenario where i want to order the table and arrange by groups having highest to lowest sum

 name   score
----------------
  abc    10
  pqr    9
  abc    7
  pqr    3
  abc    10
  xyz    7
  pqr    7
  xyz    3
  xyz    2

now if we observe,

total (abc) = 10 + 7 + 10 = 27

total (pqr) = 9 + 3 + 7 = 19

total (xyz) = 7 + 3 + 2 = 12

How to sort the above table in SQL by group with highest sum and it should display individual entries?

Expected output:
----------------
name   score 
----------------
abc    10
abc    10
abc    7
pqr    9
pqr    7
pqr    3
xyz    7
xyz    3
xyz    2

Answer 1

SQLite doesn't have analytical/windowed functions, so you need to work out the different pieces of data yourself.

SELECT
  yourTable.*
FROM
  yourTable
INNER JOIN
(
  SELECT name, SUM(score) AS score FROM yourTable GROUP BY name
)
  AS totalScores
    ON totalScores.name = yourTable.name
ORDER BY
  totalScores.score DESC,
  yourTable.name,
  yourTable.score   DESC

In this query, there is a sub-query. The sub-query calculates the totalScore for each name.

This then lets you put that total score in the ORDER BY clause. Note, I don't select the total score in my results, you can but it's not necessary.

Also, I've put the name in the ORDER BY. In this way, if there is a tie with more than one name sharing the same total score, the name that is first alphabetically will be shown first.