Create Combo packages - Ordered by Item Type and then Ranked as Group

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  July 30, 2018 at 12:18 pm

  #354792

  I want to create a create a Combopackage of Top 3 Hats, Top 2 T-Shirts listed by ItemType and Top QtySold. In myresults, I want to list them as they rank by ItemType as 1 group. Below is sampledata followed by desired results. Can someone help me understand how I can dothis?

  - Create Combo Pack of Top Selling Hats and T-Shirts
  -- DROP TABLE #Items

  CREATE TABLE #Items (IID int IDENTITY (1,1), Item_Desc varchar(100), ItemType varchar(10), QtySold int)

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Hat', 'Hat', 100)
  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('White Hat', 'Hat', 60)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Red Hat', 'Hat', 50)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Blue Hat', 'Hat', 40)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Green Hat', 'Hat', 35)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Teal Hat', 'Hat', 22)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Beige Hat', 'Hat', 20)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Purple Hat', 'Hat', 15)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Orange Hat', 'Hat', 5)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Brown Hat', 'Hat', 1)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('White Hat', 'T-Shirts', 190)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Hat', 'T-Shirts', 125)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Blue Hat', 'T-Shirts', 75)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Red Hat', 'T-Shirts', 56)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Green Hat', 'T-Shirts', 55)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Beige Hat', 'T-Shirts', 33)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Orange Hat', 'T-Shirts', 10)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Teal Hat', 'T-Shirts', 22)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Purple Hat', 'T-Shirts', 20)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Brown Socks', 'Socks', 110)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Socks', 'Socks', 85)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Red Socks', 'Socks', 11)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Brown Socks', 'Pants', 110)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Socks', 'Pants', 200)

  
  

  INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Beige Socks', 'Pants', 125)

  
  

   Desired Results: Create a Combo package of Top 3 Hats, Top 2 T-Shirts. The goal is to show Hats first ordered by QtySold, then show T-Shirts ordered by QtySold ordered by QtySold. Then list them all as 1 package Hats 1-3 and T-Shirts 1 & 2. 

  -- 1 Black Hat 100
  -- 2 White Hat 60
  -- 3 Red Hat 50
  -- 4 White T-Shirt 190
  -- 5 Black T-Shirt 125

  
  

• drew.allen

  SSC Guru

  Points: 76945

  More actions

  July 30, 2018 at 12:32 pm

  #1999552

  This seems very similar to a previous question of yours.  What have you already tried? And where are you getting stuck?

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  July 30, 2018 at 3:27 pm

  #1999594

  Let's try the following:
  -- Create Combo Pack of Top Selling Hats and T-Shirts

  CREATE TABLE #Items (
    IID int IDENTITY (1,1),
    Item_Desc varchar(100),
    ItemType varchar(10),
    QtySold int
);
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Hat', 'Hat', 100)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('White Hat', 'Hat', 60)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Red Hat', 'Hat', 50)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Blue Hat', 'Hat', 40)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Green Hat', 'Hat', 35)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Teal Hat', 'Hat', 22)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Beige Hat', 'Hat', 20)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Purple Hat', 'Hat', 15)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Orange Hat', 'Hat', 5)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Brown Hat', 'Hat', 1)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('White Hat', 'T-Shirts', 190)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Hat', 'T-Shirts', 125)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Blue Hat', 'T-Shirts', 75)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Red Hat', 'T-Shirts', 56)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Green Hat', 'T-Shirts', 55)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Beige Hat', 'T-Shirts', 33)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Orange Hat', 'T-Shirts', 10)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Teal Hat', 'T-Shirts', 22)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Purple Hat', 'T-Shirts', 20)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Brown Socks', 'Socks', 110)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Socks', 'Socks', 85)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Red Socks', 'Socks', 11)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Brown Socks', 'Pants', 110)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Black Socks', 'Pants', 200)
INSERT INTO #Items (Item_Desc, ItemType, QtySold) VALUES ('Beige Socks', 'Pants', 125);

  WITH TOP_HATS AS (

      SELECT TOP (3) *,
        ROW_NUMBER() OVER(ORDER BY I.QtySold DESC) AS RowNum
    FROM #Items AS I
    WHERE I.ItemType = 'Hat'
    ORDER BY I.QtySold DESC
),
    TOP_TSHIRTS AS (

          SELECT TOP (2) *,
            ROW_NUMBER() OVER(ORDER BY I.QtySold DESC) AS RowNum
        FROM #Items AS I
        WHERE I.ItemType = 'T-Shirts'
        ORDER BY I.QtySold DESC
),
    ORDERED_RESULTS AS (

          SELECT TH.RowNum, TH.Item_Desc, TH.QtySold, 1 AS GroupSort
        FROM TOP_HATS AS TH
        UNION ALL
        SELECT TS.RowNum, TS.Item_Desc, TS.QtySold, 2 AS GroupSort
        FROM TOP_TSHIRTS AS TS
)
SELECT ROW_NUMBER() OVER(ORDER BY O.GroupSort, O.RowNum) AS RowNum,
    O.Item_Desc, O.QtySold, O.GroupSort, O.RowNum
FROM ORDERED_RESULTS AS O
ORDER BY O.GroupSort, O.RowNum;

  DROP TABLE #Items;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  July 30, 2018 at 3:46 pm

  #1999599

  I suspect you could simplify that just slightly to:

  
WITH TOP_HATS AS (
   SELECT TOP (3) *, 0 As Sort
     FROM #Items AS I
     WHERE I.ItemType = 'Hat'
     ORDER BY I.QtySold DESC
  ),
  TOP_TSHIRTS AS (
     SELECT TOP (2) *, 1 As Sort
     FROM #Items AS I
     WHERE I.ItemType = 'T-Shirts'
     ORDER BY I.QtySold DESC
  )
  Select * From
  (
      Select * From TOP_HATS
      Union All Select * From TOP_TSHIRTS
  ) ALLITEMS
  Order By Sort,QtySold Desc

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  July 30, 2018 at 6:12 pm

  #1999618

  I was playing with this and did essentially the same with two CTEs. But when I first looked at it, it didn't look right. And neither did the other ones.
  I just realized, there are hats in t-shirt description, socks in pants description...i.e. 'White Hat', 'T-Shirts', 190
  The results may not look correct but they are based on the data and using the where clause with ItemType. Item_Desc will all be Hats.

  Sue

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  July 30, 2018 at 10:03 pm

  #1999620

  Yeah, the sample data seems a little bit off in that regard.

  Another approach which might perform better:
  
Select *
From
(
 Select I.*, S.Sort, S.Cnt , ROW_NUMBER() Over (Partition By I.ItemType Order By I.QtySold Desc) As position
 From #Items I
 Join (Values(0,'Hat',3),(1,'T-Shirts',2)) As S(Sort,ItemType,Cnt) On I.ItemType = S.ItemType
) X
Where X.position <= X.Cnt
Order By Sort, position