Skip to content
Advertisement

Select highest aggregated group

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

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

ID GroupDescription Sales
1 Group1 2
1 Group1 15
1 Group2 3
1 Group3 2
1 Group3 2
1 Group3 2
2 Group1 2
2 Group2 5
2 Group3 3
2 Group4 12
2 Group4 2
2 Group4 2

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:

ID GroupDescription SumSales
1 Group1 17
2 Group4 16

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

Advertisement

Answer

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

See the demo.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement