SQL Server custom sort while grouping by max sum

Question

I have 3 columns (Year, Number, SUM(Amount)) and I am trying to sort them by the max sum of amount.

SELECT TOP 1000 
    YEAR(period) AS [Year], id_number, 
    SUM(ISNULL(amount, 0)) AS [Amount]
FROM 
    table
WHERE 
    (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY 
    YEAR(period), id_number
ORDER BY 
    SUM(ISNULL(amount, 0)) DESC, id_number, YEAR

This is not the sorting I'm trying to achieve. I'd like to group them together by their id_number but sort by the max amount for all years returned. I'm guessing I might have to write a case statement but I haven't figured it out yet. I Will update if I do. I wanted to ask for help also before I rack my mind for hours on this one.

Thank you very much in advance.

Are you able to share example of desired output as that sql query looks ok to me. — d0little
– d0little, Commented May 17, 2021 at 16:01
WHERE (YEAR(period) >= 2010 AND YEAR(period) < 2021) don't use syntax like this; it's not SARGable. Use proper date logic: WHERE period >= '20100101' AND period < '20210101' — Thom A
– Thom A ♦, Commented May 17, 2021 at 16:03
So you want the groups (as denoted by id_number) to be sorted by the max value of the sums, not the individual rows. — SMor
– SMor, Commented May 17, 2021 at 16:07
Yes. I have to think out loud sometimes and my solution might be cumbersome but I have it working now and am trying everyone's suggestions here as well. — Michael Brown
– Michael Brown, Commented May 17, 2021 at 16:16

Gordon Linoff · Accepted Answer · 2021-05-17 18:36:24Z

2

Based in your description, you want to sort the ids by the maximum sum in any year. If that is the case, use window functions in the ORDER BY:

SELECT TOP 1000 YEAR(period) AS [Year], id_number, 
       SUM(ISNULL(amount, 0)) AS [Amount]
FROM table
WHERE YEAR(period) >= 2010 AND YEAR(period) < 2021
GROUP BY YEAR(period), id_number
ORDER BY MAX(SUM(ISNULL(amount, 0))) OVER (PARTITION BY id_number),
         id_number, YEAR;

The second sort key is needed so all rows for a given id_number() are together when there are ties.

answered May 17, 2021 at 18:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Brown Over a year ago

This worked perfectly. Thank you very much.

Doug Coats · Accepted Answer · 2021-05-17 16:02:06Z

1

I think this should be sufficient for you. Just wrap it in a subquery and youre good to go

SELECT * 
FROM 
    (
        SELECT TOP 1000 
            YEAR(period) AS [Year], id_number, 
            SUM(ISNULL(amount, 0)) AS [Amount]
        FROM 
            table
        WHERE 
            (YEAR(period) >= 2010 AND YEAR(period) < 2021)
        GROUP BY 
            YEAR(period), id_number
    ) x
ORDER BY 
    MAX(ISNULL(amount, 0)) DESC, id_number, YEAR

answered May 17, 2021 at 16:02

Doug Coats

7,2279 gold badges32 silver badges56 bronze badges

Comments

user212514 · Accepted Answer · 2021-05-17 16:02:09Z

1

You can reference the column by index like:

SELECT TOP 1000 
    YEAR(period) AS [Year], id_number, 
    SUM(ISNULL(amount, 0)) AS [Amount]
FROM 
    table
WHERE 
    (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY 
    YEAR(period), id_number
ORDER BY 
    3 DESC, id_number, YEAR

answered May 17, 2021 at 16:02

user212514

3,1381 gold badge17 silver badges11 bronze badges

5 Comments

Thom A Over a year ago

Bad Habits to Kick : ORDER BY ordinal

SMor Over a year ago

Simply swapping the expression and the associated ordinal does not change the logic of the original query.

Michael Brown Over a year ago

Ordinal, that would be the same as calling the column right?

Jeff Over a year ago

Since the ORDER BY is processed after the SELECT - it is one of the places where you can reference the alias of the column. Example: ORDER BY [Amount] DESC

Thom A Over a year ago

No, @MichaelBrown . SELECT Col1, Col2, Col3 FROM dbo.YourTable ORDER BY Col3; and SELECT Col1, Col3 FROM dbo.YourTable ORDER BY Col3; would both work, replace ORDER BY Col3 with ORDER BY 3, however, and the latter query fails.

Michael Brown · Accepted Answer · 2021-05-17 16:12:04Z

0

Thank you much @Doug, I'm going to run that down and try it out. I also came up with a solution that might be a bit nutty but it works. LOL.

SELECT t2.row, YEAR(period) AS [Year], t1.id_number, SUM(ISNULL(amount,0)) AS [Consumption]
FROM table t1 JOIN 
(SELECT id_number, ROW_NUMBER() over (order by SUM(ISNULL(amount,0))desc) as row
FROM table
WHERE (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY id_number) t2 ON t1.id_number = t2.id_number
WHERE (YEAR(period) >= 2010 AND YEAR(period) < 2021)
GROUP BY t2.row, YEAR(period), t1.id_number
ORDER BY t2.row, year

@Larnu, I will look at fixing this. Thank you.

answered May 17, 2021 at 16:12

Michael Brown

471 silver badge6 bronze badges

Collectives™ on Stack Overflow

SQL Server custom sort while grouping by max sum

4 Answers 4

1 Comment

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related