sql sorting by subgroup sum data

Question

How sort this

a 1 15
a 2 3
a 3 34
b 1 55
b 2 44
b 3 8

to (by third column sum):

b 1 55
b 2 44
b 3 8
a 1 15
a 2 3
a 3 34

since (55+44+8) > (15+3+34)

Answer 1

If you are using SQL Server/Oracle/Postgresql you could use windowed SUM:

SELECT *
FROM tab
ORDER BY SUM(col3) OVER(PARTITION BY col) DESC, col2

LiveDemo

Output:

╔═════╦══════╦══════╗
║ col ║ col2 ║ col3 ║
╠═════╬══════╬══════╣
║ b   ║    1 ║   55 ║
║ b   ║    2 ║   44 ║
║ b   ║    3 ║    8 ║
║ a   ║    1 ║   15 ║
║ a   ║    2 ║    3 ║
║ a   ║    3 ║   34 ║
╚═════╩══════╩══════╝

Answer 2

You can do this using ANSI standard window functions. I prefer to use a subquery although this is not strictly necessary:

select col1, col2, col3
from (select t.*, sum(col3) over (partition by col1) as sumcol3
      from t
     ) t
order by sumcol3 desc, col3 desc;