Sql-server – Subtotals per category not showing data (all subtotal columns show NULL)

sql server

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.

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.

Best Answer

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.

1 Pages / 245 rows

This plan has a seek on A=1 AND B=2 with a residual predicate on (C=@C1 OR C=@C2) AND D=5

Plan 1

2 leaf Pages / 246 rows

Plan 2

In the second plan the extra operators are responsible for removing any duplicates from @C1,@C2 first before performing the seek(s).

The seek in the second plan is actually a range seek between A=1 AND B=2 AND C > Expr1010 and A=1 AND B=2 AND C < Expr1011 with a residual predicate on D=5. It still isn't an equality seek on all 4 columns. More information about the additional plan operators can be found here.

Adding OPTION (RECOMPILE) does allow it to inspect the parameter values for duplicates at compile time and produces a plan with two equality seeks.

You could also achieve that with

;WITH CTE
     AS (SELECT DISTINCT ( C )
         FROM   (VALUES (@C1),
                        (@C2)) V(C))
SELECT CA.*
FROM   CTE
       CROSS APPLY (SELECT *
                    FROM   T
                    WHERE A=1 AND B=2 AND D=5  AND C = CTE.C) CA

Plan 3

But actually in this test case it would likely be counter productive as having two seeks into the single page index rather than one increases the logical IO.

Sql-server – fn_dblog not showing up deleted table data

From Thomas LaRock at http://logicalread.solarwinds.com/sql-server-dbcc-log-command-tl01/#.VXbj_DYpDVk

"The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server; it allows you to view the transaction log records in the active part of the transaction log file for the current database."

The fact that you can no longer find your data suggests that the desired part of the log is no longer in the active part of the transaction log file.

One way to get these 4 rows back would be to restore a copy of the database to a temporary database name (e.g. DbName_Disaster) at a point in time that contained these 4 rows. Then insert the 4 rows back into your production database and drop the DbName_Disaster database.

Or you could look for some database recovery tool that fits your need. The prices and features vary.

Best Answer

Related Solutions

Sql-server – Seek predicate not using all available columns

1 Pages / 245 rows

2 leaf Pages / 246 rows

Sql-server – fn_dblog not showing up deleted table data

Related Question