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. 2805438 fiddles created (40836 in the last week).

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


CREATE TABLE timetable ( id INT NOT NULL AUTO_INCREMENT 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;
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY the_sum DESC) AS rn FROM ( SELECT SUM(quantity) AS the_sum, ' at line 10
 hidden batch(es)


DELETE FROM timetable; -- clear down the timetable data
 hidden batch(es)


INSERT INTO timetable (quantity, dt_start,dt_end) VALUES (100, '2021-01-14 10:00:00', '2021-01-16 10:00:00'), (125, '2021-01-14 10:00:00', '2021-01-16 10:00:00'), (200, '2021-01-17 10:00:00', '2021-01-23 10:00:00'), (50, '2021-01-14 10:00:00', '2021-01-19 10:00:00'), (100, '2021-01-21 10:00:00', '2021-01-23 10:00:00'), (75, '2021-01-16 10:00:00', '2021-01-21 10:00:00'), (100, '2021-01-16 10:00:00', '2021-01-19 10:00:00'), (200, '2021-01-16 10:00:00', '2021-01-19 10:00:00'), (50, '2021-01-18 10:00:00', '2021-01-19 10:00:00'), (150, '2021-01-21 10:00:00', '2021-01-23 10:00:00'), (220, '2021-01-25 10:00:00', '2021-01-25 10:00:00');
 hidden batch(es)


SELECT * FROM timetable;
id quantity dt_start dt_END
11 100 2021-01-14 10:00:00 2021-01-16 10:00:00
12 125 2021-01-14 10:00:00 2021-01-16 10:00:00
13 200 2021-01-17 10:00:00 2021-01-23 10:00:00
14 50 2021-01-14 10:00:00 2021-01-19 10:00:00
15 100 2021-01-21 10:00:00 2021-01-23 10:00:00
16 75 2021-01-16 10:00:00 2021-01-21 10:00:00
17 100 2021-01-16 10:00:00 2021-01-19 10:00:00
18 200 2021-01-16 10:00:00 2021-01-19 10:00:00
19 50 2021-01-18 10:00:00 2021-01-19 10:00:00
20 150 2021-01-21 10:00:00 2021-01-23 10:00:00
21 220 2021-01-25 10:00:00 2021-01-25 10:00:00
 hidden batch(es)


CREATE TABLE calendar (dt date not null primary key); INSERT INTO calendar (dt) VALUES ('2021-01-12'), ('2021-01-13'), ('2021-01-14'), ('2021-01-15'), ('2021-01-16'), ('2021-01-17'), ('2021-01-18'), ('2021-01-19'), ('2021-01-20'), ('2021-01-21'), ('2021-01-22'), ('2021-01-23'), ('2021-01-24'), ('2021-01-25'), ('2021-01-26'), ('2021-01-27'), ('2021-01-28');
 hidden batch(es)


SELECT t.quantity, SUBSTR(CAST(YEAR(t.dt_start) AS CHAR), 1, 4) AS "year", CAST(MONTH(t.dt_start) AS CHAR) AS "month", CAST(DAY(t.dt_start) AS CHAR) AS "day" -- (CAST(YEAR(t.dt_start) + '-' + CAST(MONTH(t.dt_start) AS CHAR)) FROM timetable t ORDER BY dt_start;
quantity year month day
100 2021 1 14
125 2021 1 14
50 2021 1 14
75 2021 1 16
100 2021 1 16
200 2021 1 16
200 2021 1 17
50 2021 1 18
100 2021 1 21
150 2021 1 21
220 2021 1 25
 hidden batch(es)


SELECT SUM(t.quantity), SUBSTR(CAST(YEAR(t.dt_start) AS CHAR), 1, 4) AS "year", CAST(MONTH(t.dt_start) AS CHAR) AS "month", CAST(DAY(t.dt_start) AS CHAR) AS "day" -- (CAST(YEAR(t.dt_start) + '-' + CAST(MONTH(t.dt_start) AS CHAR)) FROM timetable t GROUP BY dt_start ORDER BY dt_start;
SUM(t.quantity) year month day
275 2021 1 14
375 2021 1 16
200 2021 1 17
50 2021 1 18
250 2021 1 21
220 2021 1 25
 hidden batch(es)


SELECT c.dt AS "The date:", COALESCE(t1.the_sum, 0) AS "Sum from:" FROM calendar c LEFT JOIN ( SELECT SUM(t.quantity) AS the_sum, t.dt_start AS dat FROM timetable t GROUP BY dt_start ) AS t1 ON c.dt = CAST(t1.dat AS DATE) ORDER BY c.dt;
The date: Sum from:
2021-01-12 0
2021-01-13 0
2021-01-14 275
2021-01-15 0
2021-01-16 375
2021-01-17 200
2021-01-18 50
2021-01-19 0
2021-01-20 0
2021-01-21 250
2021-01-22 0
2021-01-23 0
2021-01-24 0
2021-01-25 220
2021-01-26 0
2021-01-27 0
2021-01-28 0
 hidden batch(es)