0

I would like to understand how to order descending a group of column values based on the sum of another column from that group.

From the sample data:

mtype    location    mcount
===========================
X        TX          4
Z        AL          10
C        TX          15
M        AL          3
Z        TX          12
M        CT          4
M        TX          20
Z        CT          40

First I want to determine the SUM(mcount) by state.

location    SUM(mcount)
=======================
TX          51
CT          44
AL          13

Then based on that SUM i'd like to have the state rows with the highest SUM returned first then so on and so on.

so the above data would be returned like this (all TX rows first, then CT, then AL last)

mtype    location    mcount
===========================
X        TX          4
C        TX          15
Z        TX          12
M        TX          20
M        CT          4
Z        CT          40
Z        AL          10    
M        AL          3

The order of rows beyond the sum the group sum of their counts doesn't matter.

I have gotten as far as obtaining the list of states with the highest total mcount descending:

SELECT [LOCATION]
FROM A_TABLE
GROUP BY [STATE]
ORDER BY SUM(MCOUNT) DESC

To get my final return table should throw that into a temp table and iterate through the [STATE] values? Something like

For each [LOCATION] in temptable
    SELECT * FROM temptable tt WHERE tt.LOCATION = [LOCATION]
Next

Then somehow union the results? What is the suggested way of handling this? Thank you

Carlos Mendieta
  • 860
  • 16
  • 41

1 Answers1

1

Use a windowed SUM in the ORDER BY:

WITH YourTable AS(
    SELECT *
    FROM (VALUES('X','TX',4),
                ('Z','AL',10),
                ('C','TX',15),
                ('M','AL',3),
                ('Z','TX',12),
                ('M','CT',4),
                ('M','TX',20),
                ('Z','CT',40))V(mtype,location,mcount))
SELECT *
FROM YourTable
ORDER BY SUM(mcount) OVER (PARTITION BY location) DESC,
         mcount;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Perfect, thanks! How would you select the top 2 distinct locations from that? In this case, returning the 6 rows belonging to TX and CT. – Carlos Mendieta Sep 30 '20 at 15:40
  • 1
    That's a separate question, in my opinion, @CarlosMendieta , however, there are plenty of answers demonstrating this. For example [Get top 1 row of each group](https://stackoverflow.com/a/6841644/2029983). The logic to get the top 2 is identical. – Thom A Sep 30 '20 at 15:42