CODEWITHSUNDEEP
sqlsql-server
Behnam
Behnam

Reputation: 1063

Sorting top ten vendors and showing remained vendors as "other"

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

Answers (3)

Roger Wolf
Roger Wolf

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

Dudi Konfino
Dudi Konfino

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

mjsqu
mjsqu

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

Related Questions