Not Top N - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not Top N

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2012-05-10 : 20:36:46

I need a query to pull the top 10 Vendors but all need a roll up to pull and group all the other Vendors into one row. So I have the top 10 but know need to write the query for the not top 10. I figured just to do a Union All to combine both of them together.

Lets say the columns are Vendor, Amount

Gregory Cardullo

cardullo4321
Starting Member

40 Posts

Posted - 2012-05-10 : 23:14:55

select ename, sal
From
(
Select ename
,sal
,Rank() OVER (Partition by sal Order by SAL DESC) AS newRank
,RANK() OVER (ORDER BY Sal DESC) as newnewrank
,Dense_RANK() OVER (ORDER BY Sal DESC) as Denserank
fROM [GregAutomation].[dbo].[EMP]) Testing

wHERE NewNewRank <= 10

Union All

Select ename = 'All Other Vendors', sum(sal) as Sal
From
(
select ename, sum(sal) as Sal
From
(
Select ename
,sal
,Rank() OVER (Partition by sal Order by SAL DESC) AS newRank
,RANK() OVER (ORDER BY Sal DESC) as newnewrank
,Dense_RANK() OVER (ORDER BY Sal DESC) as Denserank
fROM [GregAutomation].[dbo].[EMP]) Testing
wHERE NewNewRank > 10
group by testing.ename)
TestingTwo

Gregory Cardullo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 23:18:03

[code]
SELECT CASE WHEN rnk > 10 THEN 'All Other Vendors' ELSE ename END,
SUM(sal) AS TotalSal
FROM
(
SELECT RANK() OVER (ORDER BY Sal DESC) as rnk,*
FROM table
)t
GROUP BY CASE WHEN rnk > 10 THEN 'All Other Vendors' ELSE ename END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources