MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'oligoname + fluorophore wavelength',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='ReadoutProbes for mFISH Survey';

CREATE TABLE `item_containers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `item_id` int NOT NULL COMMENT 'content of tube',
  `volume` float(12,2) NOT NULL COMMENT 'volume in micro liter (uL)',
  PRIMARY KEY (`id`),
  KEY `fk_item_containers_items` (`item_id`),
  CONSTRAINT `fk_item_containers_items` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=764 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical tubes received from vendor';

CREATE TABLE `itemkits` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `Unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1030 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='A readout kit is a collection of readouts, and defined in a codebook';

CREATE TABLE `itemkit_containers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `itemkit_id` int NOT NULL,
  `populated` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Field used for checking in checking out a tray',
  PRIMARY KEY (`id`),
  KEY `fk_readoutkit_tray_readoutkits` (`itemkit_id`),
  CONSTRAINT `fk_readoutkit_tray_readoutkits` FOREIGN KEY (`itemkit_id`) REFERENCES `itemkits` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1027 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Physical readoutkit_tray';

CREATE TABLE `itemkit_item` (
  `itemkit_id` int NOT NULL,
  `item_id` int NOT NULL,
  UNIQUE KEY `Uniqueness` (`itemkit_id`,`item_id`),
  KEY `fk_readoutkit_item_readout_probes` (`item_id`),
  CONSTRAINT `fk_readoutkit_item_readout_probes` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_readoutkit_item_readoutkits` FOREIGN KEY (`itemkit_id`) REFERENCES `itemkits` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='associations table for definition of a readout kit';


insert  into `items`(`id`,`name`) values 
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D');

insert  into `itemkits`(`id`,`name`) values 
(1,'Kit_1'),
(2,'Kit_2');

insert  into `itemkit_containers`(`itemkit_id`,`populated`) values 
(1,0),
(2,1);

insert  into `itemkit_item`(`itemkit_id`,`item_id`) values 
(1,1),
(1,3),
(2,2);

insert  into `item_containers`(`item_id`,`volume`) values 
(1,1.00),
(2,1.00),
(3,1.00),
(4,1.00),
(1,1.00);


Records: 4  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0



Records: 5  Duplicates: 0  Warnings: 0


SELECT * from items;
SELECT item_id, volume from item_containers;
SELECT * FROM itemkits;
SELECT itemkit_id, populated FROM itemkit_containers;
SELECT * FROM itemkit_item;


id
name


1
A

2
B

3
C

4
D




item_id
volume


1
1.00

2
1.00

3
1.00

4
1.00

1
1.00




id
name


1
Kit_1

2
Kit_2




itemkit_id
populated


1
0

2
1




itemkit_id
item_id


1
1

2
2

1
3


     select i.id,i.name,sum(ic.volume) as total_volume,
     sum(coalesce(ii.item_count,0)) as Reserved 
     from items i inner join item_containers ic on i.id=ic.item_id
     left join (select item_id,count(*) as item_count from itemkit_containers ic
     inner join itemkit_item i on ic.itemkit_id =i.itemkit_id and ic.populated=1
     group by item_id) ii
     on i.id=ii.item_id
     group by i.id,i.name
     order by i.id,i.name


id
name
total_volume
Reserved


1
A
2.00
0

2
B
1.00
1

3
C
1.00
0

4
D
1.00
0