0

In my query I have one last obstacle I'm trying to overcome. My goal is to group products ("prod") by the specified category ("prodcat") and have a subtotal at the end of each category.

Below I included a picture of the data of each column/category associated with each item (B/O, On Hand, Commit, [Tot Avail], Jan, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV AND DEC). That data associated with each prodcat needs to be subtotaled at the end of each category. But as you can see, while I have the 'Subtotal - prodcat' working as intended, it is NULL in each of the related columns for that row.

enter image description here

I have a similar issue with the [Overall Total] that needs to be at the very end to calculate all the prodcats data. However as you can see its at the very top of the list. I can't seem to figure out what I'm doing wrong in either case.

I have tried using CASE WHEN, GROUPING SET, ROLLUP and other things yet nothing has worked.

What am I doing wrong?

The query is below:

 SELECT [Prodct] = ROW_NUMBER() over (Partition By prodcat order by case 
    when prod LIKE '%' then 1
    when prod LIKE '%total%' then 2 
    else 3
end)
    ,prod
          ,[prodcat] = CASE 
            WHEN prod IS NULL THEN 
                '[Subtotal - ' + COALESCE(prodcat, 'Overall') + ']' 
            ELSE prodcat
            END
          ,[vendprod] As 'Vendor #'
          ,[qtybo] AS 'B/O'
          ,[qtyonhand] AS 'On Hand'
          ,[qtycommit] ' Commit'
          ,[Tot Avail]
          ,[JAN]
          ,[FEB]
          ,[MAR]
          ,[APR]
          ,[MAY]
          ,[JUN]
          ,[JUL]
          ,[AUG]
          ,[SEP]
          ,[OCT]
          ,[NOV]
          ,[DEC]
      FROM
        (SELECT 
          [prod]
          ,[vendprod]
          ,[qtybo]
          ,[qtyonhand]
          ,[qtycommit]
          ,[Tot Avail]
          ,SUM(CAST(JAN AS int)) over (PARTITION BY prod) AS JAN
          ,SUM(CAST(FEB AS int)) over (PARTITION BY prod) AS FEB
          ,SUM(CAST(MAR AS int)) over (PARTITION BY prod) AS MAR
          ,SUM(CAST(APR AS int)) over (PARTITION BY prod) AS APR
          ,SUM(CAST(MAY AS int)) over (PARTITION BY prod) AS MAY
          ,SUM(CAST(JUN AS int)) over (PARTITION BY prod) AS JUN
          ,SUM(CAST(JUL AS int)) over (PARTITION BY prod) AS JUL
          ,SUM(CAST(AUG AS int)) over (PARTITION BY prod) AS AUG
          ,SUM(CAST(SEP AS int)) over (PARTITION BY prod) AS SEP
          ,SUM(CAST(OCT AS int)) over (PARTITION BY prod) AS OCT
          ,SUM(CAST(NOV AS int)) over (PARTITION BY prod) AS NOV
          ,SUM(CAST(DEC AS int)) over (PARTITION BY prod) AS 'DEC'
          ,CAST(ROW_NUMBER() OVER (PARTITION BY prod ORDER BY prod) AS INT) count
          ,prodcat
      FROM [databasename].[dbo].[Report])  AS sub
      where count = 1 AND prodcat is not null
      GROUP BY ROLLUP ((prodcat), (prod, vendprod, qtybo, qtyonhand, qtycommit, [Tot Avail], JAN, FEB, MAR,[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC]))

Edit: As suggested by Ronaldo I went ahead and used SQL Fiddle to 're-create' what I'm trying to do. I've never used SQL Fiddle before so it took some time for me to do it. The one thing I will say is that SQL Fiddle doesn't let you 'create views'.

 CREATE TABLE icsw (
    prod varchar(88) not null,
    vendprod varchar(48) null,
    qtyonhand decimal(17,2) null,
    qtycommit decimal(17,2) null,
    cono int not null,
    statustype varchar(42) null,
    whse varchar(48) not null,
    PRIMARY KEY (prod, cono, whse)
  );
  
  CREATE TABLE smsw (
    componentfl bit(1) not null,
    cono int not null,
    prod varchar(88),
    whse varchar(88) not null,
    yr int not null,
    qtysold varchar(404) null,
    insx bit null,
    PRIMARY KEY (componentfl, cono, prod, whse, yr)
   );
  
  CREATE TABLE icsp (
    prod varchar(88) not null,
    prodcat varchar(48) null,
    kittype varchar(48) null,
    cono int not null,
    PRIMARY KEY (prod, cono)
  );
  
    INSERT INTO icsp
    (`prod`, `prodcat`, `kittype`, `cono`)
VALUES
    ('0201 0495 0135', 'RP', 'NULL', '1'),
    ('0202 0595 1135', 'TP', 'NULL', '1'),
    ('0203 1595 2135', 'LC', 'B', '1'),
    ('0204 7547 2435', 'LC', 'P', '1'),
    ('playstation 5', 'BATT', 'NULL', '1'),
    ('playstation 4', 'BATT', 'NULL', '1'),
    ('playstation 3', 'BATT', 'NULL', '1'),
    ('playstation 2', 'BATT', 'NULL', '1'),
    ('xbox 360', 'BATT', 'NULL', '1'),
    ('xbox One', 'BATT', 'NULL', '1'),
    ('Zeus Battery Backup', 'BATT', 'NULL', '1'),
    ('N64', 'BATTN', 'NULL', '1'),
    ('Super Nintendo', 'BATTN', 'NULL', '1'),
    ('Super Nintendo KIT', 'BATTN', 'B', '1'),
    ('Backlight', 'LB', 'NULL', '1'),
    ('Dell Custom Laptop 1', 'AB50', 'P', '1'), 
    ('Dell Custom Laptop KIT', 'AB50', 'P', '1'),
    ('Run from Zombies Training Course', 'PT', 'NULL', '1'),
    ('Run from BOSS MUSIC Training Course', 'PT', 'NULL', '1'),
    ('Dont say anything crazy Training Course', 'PR', 'NULL', '1'),
    ('New York Yankees Spring Training Schedule', 'PR', 'NULL', '0')
;

  INSERT INTO smsw
    (`componentfl`, `cono`, `prod`, `whse`, `yr`, `qtysold`, `insx`)
VALUES
    (0, '1', '0201 0495 0135', 'NY', '98', '102;114;132;23;37;39;13;36;16;10;0;0;0', 1),
    (0, '1', '0202 0595 1135', 'NY', '99', '-3;0;1;0;0;3;0;0;0;0;0;0;0', 1),
    (0, '1', '0203 1595 2135', 'NY', '1', '1;4;1;0;1;3;0;3;0;1;0;0;0', 1),
    (0, '1', '0204 7547 2435', 'NY', '20', '0;8;0;1;4;0;0;1;0;0;0;0;0', 1),
    (0, '1', 'playstation 5', '', '20', '44;31;31;52;39;50;46;37;0;0;0;0;0', 1),
    (0, '1', 'playstation 4', '', '20', '39;78;37;21;20;24;78;28;40;14;0;0;0', 1),
    (0, '1', 'playstation 3', '', '20', '72;54;63;43;61;49;37;48;19;22;0;0;0', 1),
    (0, '1', 'playstation 2', '', '99', '176;190;164;164;160;212;231;137;163;81;0;0;0', 1),
    (0, '1', 'xbox 360', '', '20', '41;28;31;35;73;57;67;49;46;32;0;0;0', 1),
    (0, '1', 'xbox One', '', '20', '0;0;2;2;6;10;5;4;3;0;0;0;0', 1),
    (0, '1', 'Zeus Battery Backup', '', '20', '0;0;5;7;15;10;25;14;33;0;0;0;0', 1),
    (0, '1', 'N64', '', '96', '60;24;26;35;73;72;43;27;34;28;0;0;0', 1),
    (0, '1', 'Super Nintendo', '', '93', '64;49;45;70;94;88;71;89;118;51;0;0;0', 1),
    (1, '1', 'Super Nintendo KIT', '', '95', '2;0;0;0;2;0;1;4;1;5;0;0;0', 1),
    (1, '1', 'Backlight', 'NY', '95', '4;1;3;6;1;6;2;6;3;4;0;0;0', 1),
    (0, '1', 'Dell Custom Laptop 1', 'NY', '20', '82;36;38;51;62;34;63;84;56;40;0;0;0', 1), 
    (1, '1', 'Dell Custom Laptop 1 KIT', 'NY', '20', '13;0;20;36;0;0;0;0;0;0;0;0;0', 1),
    (0, '1', 'Run from Zombies Training Course', 'NY', '20', '32;0;45;125;8;45;28;53;-11;30;0;0;0', 1),
    (0, '1', 'Run from BOSS MUSIC Training Course', 'NY', '80', '11;26;9;6;5;21;23;6;25;1;0;0;0', 1),
    (0, '1', 'Dont say anything crazy Training Course', 'NY', '20', '14;36;11;5;7;20;3;6;22;2;0;0;0', 1),
    (0, '0', 'New York Yankees Spring Training Schedule', 'NY', '20', '0;0;0;0;0;0;0;0;0;0;2000;0;0', 1)
;

 INSERT INTO icsw
    (`cono`, `prod`, `vendprod`, `qtyonhand`, `qtycommit`, `statustype`, `whse`)
VALUES
    ('1', '0201 0495 0135', '26971239847', '0.00', '0.00', 's','NY'),
    ('1', '0202 0595 1135', '38137234999', '14.00', '0.00', 's', 'NY'),
    ('1', '0203 1595 2135', '38137234999', '7.00', '2.00', 's', 'NY'),
    ('1', '0204 7547 2435', '38137234999', '3.00', '0.00', 's', 'NY'),
    ('1', 'playstation 5', '73293687625', '0.00', '0.00', 'x', 'NY'),
    ('1', 'playstation 4', '73293687625', '10000.00', '5000.00', 'd', 'NY'),
    ('1', 'playstation 3', '73293687625', '100.00', '50.00', 'd', 'NY'),
    ('1', 'playstation 2', '73293687625', '50.00', '5.00', 'd', 'NY'),
    ('1', 'xbox 360', '39009685421', '5000.00', '500.00', 's', 'NY'),
    ('1', 'xbox One', '39009685421', '2500.00', '250.00', 's',  'NY'),
    ('1', 'Zeus Battery Backup', '25676854322', '5.00', '2.00', 'x',  'Atlanta'),
    ('1', 'N64', '76655443322', '50.00', '10.00', 'd', 'NY'),
    ('1', 'Super Nintendo', '76655443322', '25.00', '12.00', 's', 'NY'),
    ('1', 'Super Nintendo KIT', '76655443322', '15.00', '5.00', 's','NY'),
    ('1', 'Backlight', '95395843294', '2500.00', '0.00', 'x', 'Atlanta'),
    ('1', 'Dell Custom Laptop 1', '39586749320', '52.00', '27.00', 's', 'NY'), 
    ('1', 'Dell Custom Laptop 1 KIT', '39586749320', '27.00', '10.00', 's', 'NY'),
    ('1', 'Run from Zombies Training Course', '12345678912', '500000.00', '20000.00', 'x', 'NY'),
    ('1', 'Run from BOSS MUSIC Training Course', '12345678912', '1111115.00', '25678.00', 'x', 'NY'),
    ('1', 'Dont say anything crazy Training Course', '12345678912', '9999999.00', '6666666.00', 'x', 'NY'),
    ('0', 'New York Yankees Spring Training Schedule', '55555555555', '9999.00', '66.00', 'x', 'NY')
;

CREATE VIEW dbo.Report 
AS
SELECT icsw.prod, icsw.vendprod, icsw.qtybo, icsw.qtyonhand, icsw.qtycommit, icsw.qtyonhand - icsw.qtycommit AS TotAvail, 
Split(smsw.qtysold, 1, ';') AS JAN, Split(smsw.qtysold, 2, ';') AS FEB, Split(smsw.qtysold, 3, ';') AS MAR, Split(smsw.qtysold, 4, ';') AS APR, Split(smsw.qtysold, 5, ';') AS MAY, Split(smsw.qtysold, 
6, ';') AS JUN, Split(smsw.qtysold, 7, ';') AS JUL, Split(smsw.qtysold, 8, ';') AS AUG, Split(smsw.qtysold, 9, ';') AS SEP, Split(smsw.qtysold, 10, ';') AS OCT, 
Split(smsw.qtysold, 11, ';') AS NOV, Split(smsw.qtysold, 12, ';') AS 'DEC', icsp.kittype, icsp.prodcat, icsw.cono, smsw.componentfl
FROM icsw LEFT OUTER JOIN
     icsp ON icsp.prod = icsw.prod LEFT OUTER JOIN
     smsw ON icsp.prod = smsw.prod
WHERE        (icsp.cono = 1) AND (icsp.prodcat = 'TL' OR
                         icsp.prodcat = 'AS' OR
                         icsp.prodcat = 'RP' OR
                         icsp.prodcat = 'TP' OR
                         icsp.prodcat = 'LC' OR
                         icsp.prodcat = 'LC' OR
                         icsp.prodcat = 'BATT' OR
                         icsp.prodcat = 'BATTN' OR
                         icsp.prodcat = 'LB' OR
                         icsp.prodcat = 'AB50' OR
                         icsp.prodcat = 'PT' OR
                         icsp.prodcat = 'PR') AND (icsp.kittype IS NULL) AND (icsw.whse = 'NY') AND (icsw.statustype = 's' OR
                         icsw.statustype = 'd' OR
                         icsw.statustype = 'x') AND (smsw.yr = 20)
GROUP BY icsw.prod, icsw.vendprod, icsw.qtybo, icsw.qtyonhand, icsw.qtycommit, icsp.kittype, icsp.prodcat, smsw.componentfl, smsw.qtysold, icsw.cono
ORDER BY icsp.prodcat, icsw.prod;

Here is the query I run, once the db / tables / View are set up:

SELECT [Prodct] = ROW_NUMBER() over (Partition By prodcat order by case 
    when prod LIKE '%' then 1
    when prod LIKE '%total%' then 2 
    else 3
end)
       ,prod
      ,[prodcat] = CASE 
        WHEN prod IS NULL THEN 
            '[Subtotal - ' + COALESCE(prodcat, 'Overall') + ']' 
        ELSE prodcat
        END
      ,[vendprod] As 'Vendor #'
      ,[qtybo] AS 'B/O'
      ,[qtyonhand] AS 'On Hand'
      ,[qtycommit] ' Commit'
      ,TotAvail
      ,[JAN]
      ,[FEB]
      ,[MAR]
      ,[APR]
      ,[MAY]
      ,[JUN]
      ,[JUL]
      ,[AUG]
      ,[SEP]
      ,[OCT]
      ,[NOV]
      ,[DEC]
  FROM
    (SELECT 
      [prod]
      ,[vendprod]
      ,[qtybo]
      ,[qtyonhand]
      ,[qtycommit]
      ,TotAvail
      ,SUM(CAST(JAN AS int)) over (PARTITION BY prod) AS JAN
      ,SUM(CAST(FEB AS int)) over (PARTITION BY prod) AS FEB
      ,SUM(CAST(MAR AS int)) over (PARTITION BY prod) AS MAR
      ,SUM(CAST(APR AS int)) over (PARTITION BY prod) AS APR
      ,SUM(CAST(MAY AS int)) over (PARTITION BY prod) AS MAY
      ,SUM(CAST(JUN AS int)) over (PARTITION BY prod) AS JUN
      ,SUM(CAST(JUL AS int)) over (PARTITION BY prod) AS JUL
      ,SUM(CAST(AUG AS int)) over (PARTITION BY prod) AS AUG
      ,SUM(CAST(SEP AS int)) over (PARTITION BY prod) AS SEP
      ,SUM(CAST(OCT AS int)) over (PARTITION BY prod) AS OCT
      ,SUM(CAST(NOV AS int)) over (PARTITION BY prod) AS NOV
      ,SUM(CAST(DEC AS int)) over (PARTITION BY prod) AS 'DEC'
      ,CAST(ROW_NUMBER() OVER (PARTITION BY prod ORDER BY prod) AS INT) count
      ,prodcat
  FROM [dbo].[Report])  AS sub
  where count = 1 AND prodcat is not null
  GROUP BY ROLLUP ((prodcat), (prod, vendprod, qtybo, qtyonhand, qtycommit, TotAvail, JAN, FEB, MAR,[APR],[MAY],[JUN],[JUL],[AUG],[SEP],[OCT],[NOV],[DEC])) 

If you find my code doesn't work, i'd be happy to run another test myself by 'creating' another/new DB' with the above information and re-running the code.

  • 2
    Hello and welcome to the forum. Please, check how to ask questions with minimal-reproducible-example so that we can better help you. Using an image as you did makes it difficult to reproduce your error. – Ronaldo Oct 16 at 20:22
  • What are you expecting from your Case statement ( when prod LIKE '%' then 1 when prod LIKE '%total%' then 2 ) ? – Dominique Boucher Oct 19 at 13:38
  • @ronaldo I went ahead and did what you suggested. – TryingtoLearn1234 Oct 20 at 12:47
  • @dominique boucher, i was just trying to get the 'subtotal - cat' to show underneath each category's data. It seemed to work in some sense but of course none of the data for the 'sub total - cat' is showing and the 'subtotal - overall' is listed at the top (and again not showing any data for the 'overall' data set of categories. I added the code that Ronaldo suggested as well. – TryingtoLearn1234 Oct 20 at 12:47
  • Cases will return the first "match". Try this quick demo and you should understand: create table #demo (txt varchar(20)); insert into #demo values ('test'),('Word'),('pizza'); select case when txt like '%' then 'my first case' when txt like '%zz%' then 'I expect pizza but it won''t show!' end "result" from #demo; drop table #demo; as your first case is like '%' (so basically everything), everything get's in that bucket – Dominique Boucher Oct 20 at 13:58
1

I figured it out. It was just a simple sum I had to do. I was overcomplicating it. For any columns I wanted to have as part of the subtotal, I just need to use sum.

When doing that, as I did below . . .

   sum([qtybo]) AS 'B/O'
  ,sum([qtyonhand]) AS 'On Hand'
  ,sum([qtycommit]) AS ' Commit'
  ,sum([Tot Avail]) AS [Tot Avail]
  ,sum([JAN]) AS JAN
  ,sum([FEB]) AS FEB
  ,sum([MAR]) AS MAR
  ,sum([APR]) AS APR
  ,sum([MAY]) AS MAY
  ,sum([JUN]) AS JUN
  ,sum([JUL]) AS JUL
  ,sum([AUG]) AS AUG
  ,sum([SEP]) AS SEP
  ,sum([OCT]) AS OCT
  ,sum([NOV]) AS NOV
  ,sum([DEC]) AS 'DEC'

The subtotal and Overall total populated with the data I was looking for.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.