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:

PartyName   Risk    SubTotal    

A           High    100
B           Med     25
A           Low     30
A           Med     70
B           Low     10

Now I want to SUM the total for each party and view the top 10. I am running into 2 problems in doing so:

1. TOP(10) on SUM

If I do the following:

SELECT TOP(10) PartyName, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName

I end up with the first 10 and not the 10 highest sums

2. ERROR:

Column 'S.Risk' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I try the following:

SELECT TOP(10) PartyName, Risk, SUM(SubTotal) Total
FROM 
    (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName

What I need is

PartyName   Risk    SubTotal    Total   

A           High    100         200
B           Med     25          35
A           Low     30          200
A           Med     70          200
B           Low     10          30

回答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 AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName
ORDER BY 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 Total per 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 AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) S
LEFT JOIN
    (SELECT PartyName, SUM(CAST(Amount AS DECIMAL)) Total
    FROM CustomerData
    GROUP BY PartyName) S2 
ON S.PartyName = S2.Partyname

回答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 AS DECIMAL)) SubTotal
    FROM CustomerData
    GROUP BY PartyName, Risk) AS S
GROUP BY PartyName, Risk

OR

SELECT TOP 10 * FROM 
(   SELECT PartyName, Risk, SUM(SubTotal) Total
   FROM 
       (SELECT PartyName, Risk, SUM(CAST(Amount AS DECIMAL))           SubTotal
       FROM CustomerData
       GROUP BY PartyName, Risk) AS S
   GROUP BY PartyName, Risk
)
ORDER BY Total DESC

回答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 AS DECIMAL)) SubTotal 
      FROM CustomerData GROUP BY PartyName, Risk) AS S 
GROUP BY PartyName
ORDER BY Total DESC