add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Uniforms (
name varchar(255),
tshirt_size SET('XS','S','M','L','XL','XXL','XXXL') NOT NULL,
tshirt_quantity INT(10) NOT NULL,
pant_size SET('XS','S','M','L','XL','XXL','XXXL') NOT NULL,
pant_quantity INT(10) NOT NULL
);
insert into Uniforms values
('Jack', 'L', 2, 'L', 2),
('John', 'XXL', 1, 'XXL', 1),
('Linda', 'S', 2, '', 0);
Records: 3  Duplicates: 0  Warnings: 0
SELECT pant_size, sum(case when pant_quantity > 0 then pant_quantity else 0 end) AS TotalTshirts
FROM Uniforms
where pant_size <> ''
GROUP BY pant_size
pant_size TotalTshirts
L 2
XXL 1
SELECT tshirt_size, sum(case when tshirt_quantity > 0 then tshirt_quantity else 0 end) AS TotalTshirts
FROM Uniforms
where tshirt_size <> ''
GROUP BY tshirt_size
tshirt_size TotalTshirts
L 2
XXL 1
S 2
SELECT 'T-Shirts' as Uniform,
sum(case when tshirt_size='XS' and tshirt_quantity > 0 then tshirt_quantity else 0 end) as 'XS',
sum(case when tshirt_size='S' and tshirt_quantity > 0 then tshirt_quantity else 0 end) as 'S',
sum(case when tshirt_size='M' and tshirt_quantity > 0 then tshirt_quantity else 0 end) as 'M',
sum(case when tshirt_size='L' and tshirt_quantity > 0 then tshirt_quantity else 0 end) as 'L',
sum(case when tshirt_size='XL' and tshirt_quantity > 0 then tshirt_quantity else 0 end) as 'XL',
sum(case when tshirt_size='XXL' and tshirt_quantity > 0 then tshirt_quantity else 0 end) as 'XXL'
FROM Uniforms
UNION ALL
SELECT 'Pants' as Uniform,
sum(case when pant_size='XS' and pant_quantity > 0 then pant_quantity else 0 end) as 'XS',
sum(case when pant_size='S' and pant_quantity > 0 then pant_quantity else 0 end) as 'S',
sum(case when pant_size='M' and pant_quantity > 0 then pant_quantity else 0 end) as 'M',
sum(case when pant_size='L' and pant_quantity > 0 then pant_quantity else 0 end) as 'L',
sum(case when pant_size='XL' and pant_quantity > 0 then pant_quantity else 0 end) as 'XL',
sum(case when pant_size='XXL' and pant_quantity > 0 then pant_quantity else 0 end) as 'XXL'
FROM Uniforms

Uniform XS S M L XL XXL
T-Shirts 0 2 0 2 0 1
Pants 0 0 0 2 0 1