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 for each column’s worth of data (such as JAN, FEB etc) at the end of each category.
Below I included a link to picture of the data. It shows each column/category associated (B/O, On Hand, Commit, [Tot Avail], Jan, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV AND DEC) and a new row called ‘subtotal – prodcat’. As I said, The data associated with each prodcat needs to be subtotaled at the end of each category. As you will see in the picture, while I have the ‘Subtotal – [prodcat]’ showing up at the end of each prodcat as intended, each column’s worth of data has NULL for that row. I removed the prod names and prodcat names but you should still clearly see the issue I’m facing.
Here is a link to an example output of what is currently happening
I can’t seem to figure out what I’m doing wrong. I have tried using CASE WHEN, GROUPING SET, ROLLUP etc and has worked. For some reason, nothing I have tried thus far allows the [subtotals – prodcat] rows (and subtotal – Overall as well) to have actual values and not just NULL.
Below I went ahead and used SQL Fiddle to ‘re-create’ what I’m trying to do. The code is the same outside of fictitious data. I’ve never used SQL Fiddle before so hopefully my write up is correct. The one thing I will say is that SQL Fiddle doesn’t let you ‘create views. However I use a ‘view’ in my process so I included the code for testing purposes below and to show EXACTLY the method I am using to get my results.
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 database / tables / View are set up:
SELECT 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.
Hopefully someone can help me in figuring out why the Subtotal rows are only showing NULL.
Advertisement
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.