Top N Values and remaining total as one summery row
-
Does anyone know how to create a query in SQL server and/or Access that gives the top 25 customers sales and the remaining total sales as one summary line. Basically the last row would be total sales for the remaining 500 customersr?
-
/*top 25 customers*/
select sales.customerid as customerid,sales.sale As TOTAL,1 AS semirank,customers.customername as customername
from dbo.sales sales
inner join
(
select TOP 25 customerid,sum(sale) as Z/*needed for union?*/
from sales
group by customerid
order by sum(sale) desc
) thebestcustomers
ON thebestcustomers.customerid=sales.customerid
inner join dbo.customers customers
on sales.customerid=customers.customerid
union all /*there will be no duplicates,appending selects*/
/*total*/
select -1 as invalid_customerid,sum(sales.sale) AS TOTAL,2 AS semirank,'BLANK' as customername
from dbo.sales sales
LEFT JOIN
(select TOP 25 customerid,sum(sale) as Z /*needed for union?*/
from sales B
group by customerid
order by sum(sale) desc
) thebestcustomers on thebestcustomers.customerid=sales.customerid
WHERE thebestcustomers.customerid IS NULL /*thebestcustomers excluded*/
order by semirank,customerid,TOTAL
*semirank introduces as sorting aid
*it will probably better to dump the bestcustomers in a temptable
-
I'd do it like this...
declare @table table
(
[id] int primary key identity(1,1),
value float
)
declare @results table
(
[id] varchar(50) primary key,
value float
)
declare @low int,
@high int
set @low = 0
set @high = 1000
while @low <= @high
begin
insert @table(value)
select @low
set @low = @low + 1
end
insert @results
select top 5 convert(varchar(50),[id]) [id],value
from @table
group by [id],value
order by value desc
select * from @results
union all
select 'TTL FOR Rest' [id],sum(value)
from
(
select convert(varchar(50),[id]) [id],value
from @table
where [id] not in
(
select [id]
from
(
select top 5 convert(varchar(50),[id]) [id],value
from @table
group by [id],value
order by value desc
 
q)
)q2
order by value desc
HTH
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply