CODEWITHSUNDEEP
sql-servergroup-bysummaxpartition-by
Cinji18
Cinji18

Reputation: 629

SQL: MAX of SUM in Each Group

Using the following code:

    SELECT [Section]
           ,[Subgroup]
           ,[ID]
           ,SUM([Amount]) AS [SUM]
    FROM Table
    GROUP BY [Section], [Subgroup], [ID]
    ORDER BY [Amount] DESC,[Section]

I was able to produce the following:

Section Subgroup    ID  SUM
A   G   1   56787
A   G   2   45687
C   W   3   36746
C   W   4   27456
C   W   5   13546
C   G   6   9345
C   T   7   8535
A   G   8   8435
C   T   9   7245
A   G   11  7243
B   T   10  6245
B   T   12  5768
C   T   13  4676
A   G   14  3564
A   G   16  3465
C   T   15  2576
B   T   17  2478
A   S   18  1856
C   W   19  1567
B   T   20  1244
A   C   22  1146
C   T   21  1056
C   T   23  945
C   T   24  933
B   G   27  845
C   G   25  834
A   A   26  779
B   T   28  742
C   C   29  734
B   G   31  689
C   T   30  623
A   S   33  587
A   S   34  565
A   E   32  489
B   T   35  478
C   G   37  378
B   T   39  256

But what I want to get is the highest sum for each Subgroup in each Section:

Section Subgroup    ID  SUM
A   G   1   56787
A   S   18  1856
A   C   22  1146
A   A   26  779
A   E   32  489
B   T   10  6245
B   G   27  845
C   W   3   36746
C   G   6   9345
C   T   7   8535
C   C   29  734

I tried using 'OVER' and 'PARTITION BY' but couldn't get them to work. I looked into 'RANK' too. What am I missing?

Thanks.

Upvotes: 0

Views: 1701

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

Try this:

SELECT  [Section], [Subgroup], [ID], [SUM]
FROM (       
   SELECT [Section], [Subgroup], [ID], SUM([Amount]) AS [SUM],
          ROW_NUMBER() OVER (PARTITION BY [Section], [Subgroup] 
                             ORDER BY SUM([Amount]) DESC) As rn
   FROM Table
   GROUP BY [Section], [Subgroup], [ID]) AS t
WHERE t.rn = 1
ORDER BY [Section], [Subgroup]

Upvotes: 1

Related Questions