SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE data( device INT NOT NULL, date DATETIME NOT NULL, metric NUMERIC(10,2) NOT NULL ); INSERT INTO data VALUES (1, '2022-01-01 00:00:00', 2.34), (2, '2022-01-01 00:00:00', 1.23), (3, '2022-01-01 00:00:00', 3.82), (4, '2022-01-01 00:00:00', 0.45), (1, '2022-01-01 01:00:00', 2.00), (2, '2022-01-01 01:00:00', 1.82), (3, '2022-01-01 01:00:00', 3.09); SELECT date, SUM(d1), SUM(d2), SUM(d3), SUM(d4) FROM ( SELECT date, CASE WHEN device=1 THEN metric ELSE NULL END AS d1, CASE WHEN device=2 THEN metric ELSE NULL END AS d2, CASE WHEN device=3 THEN metric ELSE NULL END AS d3, CASE WHEN device=4 THEN metric ELSE NULL END AS d4 FROM data ) nested GROUP BY date;