I'm having trouble with selecting the highest aggregated group.

I have data in a table like this: Sales table:

I want to return 1 record for each ID. I also want to include the Group that had the most sales and the total sales for that group and ID.

Expected output:

I have code working but I feel like it can be written much better:

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID, SumSales DESC) as RowNum, * FROM
    (
        SELECT
        ID 
        ,GroupDescription
        ,SUM(Sales) OVER(PARTITION BY ID,GroupDescription) as SumSales 
        FROM Sales
        ) t1
    ) t2
WHERE RowNum = 1

CodePudding user response：

Aggregate by ID and GroupDescription and use window functions FIRST_VALUE() and MAX() to get the top group and its total:

SELECT DISTINCT ID,
       FIRST_VALUE(GroupDescription) OVER (PARTITION BY ID ORDER BY SUM(Sales) DESC) GroupDescription,
       MAX(SUM(Sales)) OVER (PARTITION BY ID) SumSales
FROM Sales
GROUP BY ID, GroupDescription;

See the demo.

CodePudding user response：

Seems like you could use normal aggregation in the inner table. You can also put the row-number on the same level as that.

SELECT
  s.ID
  ,s.GroupDescription
  ,s.SumSales
FROM
(
    SELECT
      s.ID
      ,s.GroupDescription
      ,SUM(s.Sales) as SumSales
      ,ROW_NUMBER() OVER (PARTITION BY s.ID ORDER BY SUM(s.Sales) DESC) as RowNum
    FROM Sales s
    GROUP BY
      s.ID
      ,s.GroupDescription
) s
WHERE s.RowNum = 1;

db<>fiddle

Note that ordering a window function by the same column as the partitioning makes no sense, and will be ignored.