Reputation: 1063
Please consider a table of vendors having two columns: VendorName
and PayableAmount
I'm looking for a query which returns top ten vendors sorted by PayableAmount
descending and sum of other payable amounts as "other" in 11th row.
Obviously, sum of PayableAmount
from Vendors
table should be equal to sum of PayableAmount
from Query
.
Upvotes: 0
Views: 157
Reputation: 7722
Technically, it's possible to do in one query:
declare @t table (
Name varchar(50) primary key,
Amount money not null
);
-- Dummy data
insert into @t (Name, Amount)
select top (20) sq.*
from (
select name, max(number) as [Amount]
from master.dbo.spt_values
where number between 100 and 100000
and name is not null
group by name
) sq
order by newid();
-- The table itself, for verification
select * from @t order by Amount desc;
-- Actual query
select top (11)
case when sq.RN > 10 then '<All others>' else sq.Name end as [VendorName],
case
when sq.RN > 10 then sum(sq.Amount) over(partition by case when sq.rn > 10 then 1 else 0 end)
else sq.Amount
end as [Value]
from (
select t.Name, t.Amount, row_number() over(order by t.Amount desc) as [RN]
from @t t
) sq
order by sq.RN;
It will even work on any SQL Server version starting with 2005. But, in real life, I would prefer to calculate these 2 parts separately and then UNION
them.
Upvotes: 1
Reputation: 1136
this is for the 11th row
i didnt check it
declare @i int
set @i=
(select sum(x.PayableAmount)
from
(select * from table
except
select top 10 *from table
order by PayableAmount desc) as x)
select 'another',@i
Upvotes: 0
Reputation: 5452
This would perform the query you're looking for. Firstly extracting those in the top 10, then UNION
ing that result with the higher ranked vendors, but calling those 'Other'
WITH rank AS (SELECT
VendorName,
PayableAmount,
ROW_NUMBER() OVER (ORDER BY PayableAmount DESC) AS rn
FROM vendors)
SELECT VendorName,
rn,
PayableAmount
FROM
rank WHERE rn <= 10
UNION
SELECT VendorName, 11 AS rn, PayableAmount
FROM
(
SELECT 'Other' AS VendorName,
SUM(PayableAmount) AS PayableAmount
FROM
rank WHERE rn > 10
) X11
ORDER BY rn
This has been tested in SQLFiddle.
Upvotes: 0