Malenkymuk Malenkymuk -4 years ago 45
SQL Question

sql sorting by subgroup sum data

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 Source

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 ║
╚═════╩══════╩══════╝
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download