Select Top(10) With Nested Group By

I have a query which works fine and delivers the result SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal FROM CustomerData GROUP BY PartyName, Risk Resulting data: Pa

Solution 1:

Issue 1:

If you want the "highest 10 subtotals" then you need an ORDER BY.

SELECT TOP(10) PartyName, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount ASDECIMAL)) SubTotal
    FROM CustomerData
    GROUPBY PartyName, Risk) AS S
GROUPBY PartyName
ORDERBY Total DESC

---

Issue 2:

This gets a bit tricky, because you want to GROUP BY both PartyName and Risk while summing the SubTotal, however you also want to sum the SubTotal per PartyName without rolling them up.

One way to do this would be to join the table to another table that's nearly identical, however the second one will select the Totalper Party (disregarding Risk entirely), so that we can get the grouped totals.

We can then merge that with our initial query ON PartyName to have a query that returns both the rolled-up data, as well as repeating Total per Party.

SELECT TOP(10) s.PartyName, s.Risk, s.SubTotal, s2.Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount ASDECIMAL)) SubTotal
    FROM CustomerData
    GROUPBY PartyName, Risk) S
LEFTJOIN
    (SELECT PartyName, SUM(CAST(Amount ASDECIMAL)) Total
    FROM CustomerData
    GROUPBY PartyName) S2 
ON S.PartyName = S2.Partyname

Solution 2:

If Risk is needed in the outer query, GROUP BY it at the bottom.

SELECT TOP(10) PartyName, Risk, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount ASDECIMAL)) SubTotal
    FROM CustomerData
    GROUPBY PartyName, Risk) AS S
GROUPBY PartyName, Risk

OR

SELECT TOP 10 * FROM 
(   SELECT PartyName, Risk, SUM(SubTotal) Total
   FROM 
       (SELECT PartyName, Risk, SUM(CAST(Amount ASDECIMAL))           SubTotal
       FROM CustomerData
       GROUPBY PartyName, Risk) AS S
   GROUPBY PartyName, Risk
)
ORDERBY Total DESC

Solution 3:

In the first statement that u used, that with the TOP (10), Add at the end: ORDER BY Total DESC. Thats all!:

SELECT TOP(10) PartyName, SUM(SubTotal) Total 
FROM (SELECT PartyName, Risk, SUM(CAST(Amount ASDECIMAL)) SubTotal 
      FROM CustomerData GROUPBY PartyName, Risk) AS S 
GROUPBY PartyName
ORDERBY Total DESC