SQL Server Forums - Not Top N

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

General SQL Server Forums

New to SQL Server Programming

Not Top N

New Topic

Reply to Topic

Printer Friendly

Author

Topic

cardullo4321
Starting Member

USA
40 Posts

Posted - 05/10/2012 : 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

USA
40 Posts

Posted - 05/10/2012 : 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

India
47969 Posts

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


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

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

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.