CODEWITHSUNDEEP
sqlt-sql
Sanjna Malpani
Sanjna Malpani

Reputation: 175

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

Upvotes: 1

Views: 553

Answers (3)

Tyler Roper
Tyler Roper

Reputation: 21672

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

Upvotes: 3

Rostan
Rostan

Reputation: 153

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

Upvotes: 0

DanielG
DanielG

Reputation: 1675

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

Upvotes: 0

Related Questions