ROLLUP can be used here?

• ganeshmuthuvelu

  SSCrazy

  Points: 2177

  More actions

  June 10, 2017 at 9:42 am

  #323969

  Hello:
  Let's say I have a table with the following sales data.

  
drop table #values
create table #values(product_name varchar(50), total_sale numeric(18,2))

  insert into #values select 'P1', 100
insert into #values select 'P1', 10
insert into #values select 'P2', 200
insert into #values select 'P3', 300
insert into #values select 'P3', 30
insert into #values select 'P4', 400
insert into #values select 'P5', 500
insert into #values select 'P6', 600
insert into #values select 'P7', 10
insert into #values select 'P8', 20

  select product_name, sum(total_sale) from #values group by product_name

  --Get the top 5 products by total sales and group the others with "Others" such that the results are:
P6    600.00
P5    500.00
P4    400.00
P3    330.00
P2    200.00
P1    110.00
Others 30.00


  I want to get the top 5 products by total sales and sum the other products into the "Others" category. as shown above.. 
  I'm using SQL Serverv 2016 and I wonder how the SQL Server 2012's window functionality can be used to get the above results?

  Thanks!

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  June 10, 2017 at 5:54 pm

  #1946308

  This will give you the same output as that which you posted...

  
DECLARE @OtherLimit INT = 6
;
   WITH ctePreAgg AS
(
 SELECT  product_name = CASE
                            WHEN DENSE_RANK() OVER (ORDER BY SUM(total_sale) DESC) <= @OtherLimit
                            THEN product_name
                            ELSE 'Others'
                        END
        ,total_sale = SUM(total_sale)
   FROM #values
  GROUP BY product_name
)
 SELECT  product_name
        ,sum_total_sale = SUM(total_sale)
   FROM ctePreAgg
  GROUP BY product_name
  ORDER BY sum_total_sale DESC
;

  Change the "6" to a "5" in the DELCLARE/Variable Assignment to give you what you asked for verbally.  You returned the top 6 but asked for the top 5.
  

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions