By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE subgroups (subgroup_abbr_str VaRCHAR(2));
INSERT INTO subgroups VALUES ('AA'),
('BB'),
('BB'),
('CC'),
('DD'),
('DD'),
('DD')
Records: 7 Duplicates: 0 Warnings: 0
CREATE tABLE trad_new (present INT)
INSERT INTO trad_new VALUEs (1)
CREATE PROCEDURE FLAGITERATION()
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE _subgroup varchar(100) DEFAULT "";
-- declare cursor for subgroup_abbr_str
DEClARE cursubgroup
CURSOR FOR
SELECT DISTINCT subgroup_abbr_str FROM subgroups;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
OPEN cursubgroup;
getsubgroup: LOOP
FETCH cursubgroup INTO _subgroup;
IF finished = 1 THEN
LEAVE getsubgroup;
END IF;
SET @a := _subgroup;
-- build email list
SET @sql = CONCAT("CREATE TEMPORARY TABLE ",_subgroup," AS SELECT SUM(present)
FROM trad_new;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
END LOOP getsubgroup;
CLOSE cursubgroup;
DEALLOCATE PREPARE stmt;
End
call FLAGITERATION()
SELECT * FROM AA;
| SUM(present) |
|---|
| 1 |