clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805464 fiddles created (40815 in the last week).

select version();
version()
8.0.23
 hidden batch(es)


CREATE TABLE timetable ( id INT PRIMARY KEY, quantity INT NOT NULL, dt_start DATETIME, dt_END DATETIME );
 hidden batch(es)


INSERT INTO timetable (id, quantity, dt_start,dt_end) VALUES (1, 100, '2021-01-14 10:00:00', '2021-01-16 10:00:00'), (2, 125, '2021-01-14 10:00:00', '2021-01-16 10:00:00'), (3, 200, '2021-01-17 10:00:00', '2021-01-23 10:00:00'), (4, 50, '2021-01-14 10:00:00', '2021-01-19 10:00:00'), (5, 100, '2021-01-21 10:00:00', '2021-01-23 10:00:00'), (6, 75, '2021-01-16 10:00:00', '2021-01-21 10:00:00'), (7, 100, '2021-01-16 10:00:00', '2021-01-19 10:00:00'), (8, 200, '2021-01-16 10:00:00', '2021-01-19 10:00:00'), (9, 50, '2021-01-18 10:00:00', '2021-01-19 10:00:00'), (10, 150, '2021-01-21 10:00:00', '2021-01-23 10:00:00');
 hidden batch(es)


SELECT * FROM timetable ORDER BY dt_start, dt_end;
id quantity dt_start dt_END
1 100 2021-01-14 10:00:00 2021-01-16 10:00:00
2 125 2021-01-14 10:00:00 2021-01-16 10:00:00
4 50 2021-01-14 10:00:00 2021-01-19 10:00:00
7 100 2021-01-16 10:00:00 2021-01-19 10:00:00
8 200 2021-01-16 10:00:00 2021-01-19 10:00:00
6 75 2021-01-16 10:00:00 2021-01-21 10:00:00
3 200 2021-01-17 10:00:00 2021-01-23 10:00:00
9 50 2021-01-18 10:00:00 2021-01-19 10:00:00
5 100 2021-01-21 10:00:00 2021-01-23 10:00:00
10 150 2021-01-21 10:00:00 2021-01-23 10:00:00
 hidden batch(es)


-- -- Works in 5.7 -- SELECT SUM(quantity) AS the_sum, CASE WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1' WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2' ELSE 'per_3' END AS period FROM timetable GROUP BY CASE WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1' WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2' ELSE 'per_3' END ORDER BY CASE WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1' WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2' ELSE 'per_3' END, period;
the_sum period
275 per_1
625 per_2
250 per_3
 hidden batch(es)


-- Doesn't work in 5.7 - but works in 8 SELECT the_sum, period FROM ( SELECT the_sum, period, ROW_NUMBER() OVER (ORDER BY the_sum DESC) AS rn FROM ( SELECT SUM(quantity) AS the_sum, CASE WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1' WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2' ELSE 'per_3' END AS period FROM timetable GROUP BY CASE WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1' WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2' ELSE 'per_3' END ORDER BY CASE WHEN (dt_start >= '2021-01-14' AND dt_start < '2021-01-16') THEN 'per_1' WHEN (dt_start >= '2021-01-16' AND dt_start < '2021-01-19') THEN 'per_2' ELSE 'per_3' END, period ) AS t1 ) AS t2 WHERE rn = 1;
the_sum period
625 per_2
 hidden batch(es)