MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE aggs (inst varchar(100) NULL, id varchar(100) NULL);
CREATE TABLE scalars (inst varchar(100) NULL, id varchar(100) NULL, str varchar(100) NULL, `int` INT NULL, `float` FLOAT NULL);
INSERT INTO aggs VALUES 
("inst1", "id1"),
("inst2", "id2"),
("inst3", "id3"),
("inst4", "id4"),
("inst5", "id5");

INSERT INTO scalars VALUES
('inst1', 'id1', 'str1', 1, 1.1),
('inst1', 'id1', 'str2', 2, 1.2),
('inst1', 'id1', 'str3', 3, 1.3),
('inst1', 'id1', 'str4', 4, 1.4),
('inst1', 'id1', 'str5', 5, 1.5),

('inst2', 'id2', NULL, 5, 2.1),
('inst2', 'id2', NULL, 6, 2.2),
('inst2', 'id2', NULL, 7, 2.3),
('inst2', 'id2', NULL, 7, 2.4),
('inst2', 'id2', 'str6', 8, 2.5),

('inst3', 'id3', NULL, 8, 3.1),
('inst3', 'id3', NULL, 9, 3.2),
('inst3', 'id3', NULL, 10, 3.3),
('inst3', 'id3', NULL, 11, 3.4),
('inst3', 'id3', 'str7', 12, 3.5),

('inst4', 'id4', 'str8', 13, 4.1),
('inst4', 'id4', 'str9', 14, 4.2),
('inst4', 'id4', 'str10', 15, 4.3),
('inst4', 'id4', NULL, 16, 4.4),
('inst4', 'id4', 'str7', 17, 4.5),

('inst4', 'id4', 'str8', 13, 4.1),
('inst4', 'id4', 'str9', 14, 4.2),
('inst4', 'id4', 'str10', 15, 4.3),
('inst4', 'id4', NULL, 16, 4.4),
('inst4', 'id4', 'str11', 17, 4.5),

('inst5', 'id5', 'str12', 18, 5.1),
('inst5', 'id5', 'str13', 19, 5.2),
('inst5', 'id5', 'str13', 20, 5.3),
('inst5', 'id5', 'str14', 21, 5.4),
('inst5', 'id5', 'str15', 22, 5.5);
SELECT COUNT(s.`str`) AS h1_c10,
       COUNT(s.`str`) AS h1_c11,
       SUM(s.`int`) AS h1_s10,
       SUM(s.`float`) AS h1_s11,
       AVG(s.`float`) AS h1_a10,
       MAX(s.`float`) AS h1_m10,
       MIN(s.`float`) AS h1_m11
FROM aggs a INNER JOIN scalars s 
ON a.inst = s.inst AND a.id = s.id 
WHERE a.inst = 'inst1'
GROUP BY a.inst, a.id


h1_c10
h1_c11
h1_s10
h1_s11
h1_a10
h1_m10
h1_m11


5
5
15
6.5
1.3
1.5
1.1


SELECT COUNT(s.`str`) AS h1_c10,
       COUNT(s.`str`) AS h1_c11,
       SUM(s.`int`) AS h1_s10,
       SUM(s.`float`) AS h1_s11,
       AVG(s.`float`) AS h1_a10,
       MAX(s.`float`) AS h1_m10,
       MIN(s.`float`) AS h1_m11
FROM scalars s 
WHERE s.inst = 'inst1'
GROUP BY s.inst, s.id


h1_c10
h1_c11
h1_s10
h1_s11
h1_a10
h1_m10
h1_m11


5
5
15
6.5
1.3
1.5
1.1