Top N percent Desc and Top M percent Asc

Question

I am trying to get top 5 customertypes and show data for each 5 customer types, The balance (which can be any amount) I show them as "Other Customer Types". my issue is since the rows can be random and not perfectly divisible by a number then there can be repeated values in the top 5 showing up in the "Other" group which overstates the Total sales.

the Data is also being rendered in SSRS

My code using TOP PERCENT:

select final.[description], sum(final.YTDSales$) as YTDSales$

FROM(

select top 25 percent pytd2.[Description],  sum(pytd2.YTDSales$) as YTDSales$

FROM(


-- ytd sales

select re.SIC_Desc as [description],  sum((ol.NetAmt - ol.WhlOrdDiscAmt) / @exrt) AS YTDSales$

from dbo.order_line_invoice ol

INNER JOIN dbo.Vendor vd ON ol.Cono = vd.Cono AND vd.VendId = ol.VendId 
inner join Product_Warehouse pw on ol.ProdId = pw.prodid and ol.WhseId = pw.whseid and ol.cono = pw.cono 

inner join Customer c on ol.custId = c.CustId and ol.Cono = c.Cono

left join MDData.dbo.RetailEnvironment re on c.SIC = re.SIC
where ol.InvoiceDate BETWEEN @FStartDate AND @EndDate AND ol.Cono = 1 and ol.VendId IN(@Vendid) and ol.prodcatid NOT LIKE 'GP%'

group by re.SIC_Desc
)PYTD2      

group by pytd2.[description]

order by sum(pytd2.YTDSales$) DESC

UNION ALL

select top 75 percent 'Other' as 'description', sum(pytd.YTDSales$) as YTDSales$
FROM(

-- ytd sales

select re.SIC_Desc as [description], sum((ol.NetAmt - ol.WhlOrdDiscAmt) / @exrt) AS YTDSales$

from dbo.order_line_invoice ol

INNER JOIN dbo.Vendor vd ON ol.Cono = vd.Cono AND vd.VendId = ol.VendId 

inner join Product_Warehouse pw on ol.ProdId = pw.prodid and ol.WhseId = pw.whseid and ol.cono = pw.cono 

inner join Customer c on ol.custId = c.CustId and ol.Cono = c.Cono

left join MDData.dbo.RetailEnvironment re on c.SIC = re.SIC

where ol.InvoiceDate BETWEEN @FStartDate AND @EndDate AND ol.Cono = 1 and ol.VendId IN(@Vendid) and ol.prodcatid NOT LIKE 'GP%'

group by re.SIC_Desc
)PYTD       

group by Ppytd.[description]

order by sum(pytd.YTDSales$)    

)final                                      

group by final.[Description]

order by sum(final.YTDSales$) DESC

my results: As you can see the Large Independent and Other has the same figure of $2280.60 in YTDQty since it is being repeated

I don't think you're going to get anywhere with this - but good luck — Strawberry, Feb 13 '16 at 15:04
So you're saying you want ties in the top 5? You're using top 25 percent in the query so does that mean you have 20 customer types or something? — shawnt00, Feb 13 '16 at 15:06
no in total i have 41 customer types...but in this query i get 32 since 9 of them did not purchase in the month of January 2016 — BuddingProgrammer, Feb 13 '16 at 17:14
So what i did is change the percentage to 15% and 84% and i got the desired results, but it may not work all the time. So i am trying to get a better answer. — BuddingProgrammer, Feb 13 '16 at 17:21
You should probably use a ranking function to number the groups and then aggregate those on `case when CustTypeRank <= 5 then CustTypeRank else -1 end` — shawnt00, Feb 14 '16 at 05:55
@shawnt00 thank yu so much. i used the rank function on both PYTD and YTD values. Did an Avg and then said <= 5 or >5 and now everything is fine. you should add it as an answer — BuddingProgrammer, Feb 15 '16 at 16:07

score 1 · Answer 1 · answered Feb 15 '16 at 16:35

I was picturing something like this:

with data as (
    -- your base query here grouped and summarized by customer type
), rankedData as (
    select *, row_number() over (order by YTDSales$ desc) as CustTypeRank
    from data
)
select
   case when CustTypeRank <= 5 then min("description") else 'Others' end as "description",
   sum(YTDSales$) as YTDSales$
from rankedData
group by case when CustTypeRank <= 5 then CustTypeRank else 999 end
order by case when CustTypeRank <= 5 then CustTypeRank else 999 end

score 1 · Answer 2 · answered Mar 03 '16 at 01:49

I actually used RANK instead which worked great :-

select 0 as rankytd, RANK() OVER(ORDER BY sum(ol.NetAmt - ol.WhlOrdDiscAmt) DESC) as rankpytd, re.sic, ol.VendId, vd.name, re.SIC_Desc As [description], 0 AS YTDQty, sum(ol.Quantity) AS PYTDQty
from dbo.order_line_invoice ol
INNER JOIN dbo.Vendor vd ON ol.Cono = vd.Cono AND vd.VendId = ol.VendId  
inner join dbo.Product p on ol.Cono = p.Cono and ol.prodid = p.ProdId and p.ProdCatId in (@pcat)
inner join Product_Warehouse pw on ol.ProdId = pw.prodid and ol.WhseId = pw.whseid and ol.cono = pw.cono 
inner join Customer c on ol.custId = c.CustId and ol.Cono = c.Cono
left join MDData.dbo.RetailEnvironment re on c.SIC = re.SIC  
where ol.InvoiceDate BETWEEN DATEADD(YEAR, -1,@FStartDate) AND DATEADD(YEAR, -1, @EndDate) and  ol.Cono = 1  and ol.VendId IN(@Vendid) and ol.prodcatid NOT LIKE 'GP%' 
group by re.sic, ol.VendId, vd.Name,  re.SIC_Desc

Top N percent Desc and Top M percent Asc

2 Answers2