clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36264 in the last week).

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


CREATE TABLE timetable ( id INT NOT NULL PRIMARY KEY, order_no SMALLINT NOT NULL, delivery_no INT NOT NULL, -- maybe completed_no or batch_no? Whatever! quantity SMALLINT NOT NULL, dt_start DATETIME );
 hidden batch(es)


INSERT INTO timetable (id, order_no, delivery_no, quantity, dt_start) VALUES (1, 1, 1, 100, '2021-01-14 10:00:00'), (2, 1, 2, 100, '2021-01-15 10:00:00'), (3, 1, 3, 100, '2021-01-16 10:00:00'), (4, 2, 1, 125, '2021-01-14 10:00:00'), (5, 2, 2, 125, '2021-01-15 10:00:00'), (6, 2, 3, 125, '2021-01-16 10:00:00'), ( 7, 3, 1, 200, '2021-01-17 10:00:00'), ( 8, 3, 2, 200, '2021-01-18 10:00:00'), ( 9, 3, 3, 200, '2021-01-19 10:00:00'), (10, 3, 4, 200, '2021-01-20 10:00:00'), (11, 3, 5, 200, '2021-01-21 10:00:00'), (12, 3, 6, 200, '2021-01-22 10:00:00'), (13, 3, 7, 200, '2021-01-23 10:00:00'), (14, 4, 1, 50, '2021-01-14 10:00:00'), (15, 4, 2, 50, '2021-01-15 10:00:00'), (16, 4, 3, 50, '2021-01-16 10:00:00'), (17, 4, 4, 50, '2021-01-17 10:00:00'), (18, 4, 5, 50, '2021-01-18 10:00:00'), (19, 4, 6, 50, '2021-01-19 10:00:00');
 hidden batch(es)


-- -- Works in 5.7 -- SELECT order_no AS "Order No.", ROUND(SUM(quantity), 0) AS "Tot/day/order", ROUND(AVG(quantity), 0) AS "Avg/day/order" FROM timetable GROUP BY order_no;
Order No. Tot/day/order Avg/day/order
1 300 100
2 375 125
3 1400 200
4 300 50
 hidden batch(es)


SELECT COUNT(order_no) FROM timetable GROUP BY order_no ORDER BY order_no;
COUNT(order_no)
3
3
7
6
 hidden batch(es)


-- -- Works in 5.7 -- SELECT t1.order_no AS "Order No.", t1.dt_start AS "Start Date", t2.dt_start AS "End Date", ROUND(SUM(t1.quantity), 0) AS "Total/order", ROUND(AVG(t1.quantity), 0) AS "Avg/day/order", ( SELECT COUNT(z.order_no) FROM timetable z WHERE z.order_no = t1.order_no GROUP BY order_no ORDER By order_no ) AS "No. of deliveries", (SELECT SUM(a.quantity) FROM timetable a) AS "Tot sales" FROM timetable t1 JOIN timetable t2 ON t1.dt_start = ( SELECT MIN(x.dt_start) FROM timetable x WHERE x.order_no = t1.order_no GROUP BY x.order_no ) AND t2.dt_start = ( SELECT MAX(y.dt_start) FROM timetable y WHERE y.order_no = t2.order_no GROUP BY y.order_no ) AND t1.order_no = t2.order_no GROUP BY t1.order_no, t1.dt_start, t2.dt_start ORDER BY t1.order_no, t1.dt_start, t2.dt_start;
Order No. Start Date End Date Total/order Avg/day/order No. of deliveries Tot sales
1 2021-01-14 10:00:00 2021-01-16 10:00:00 100 100 3 2375
2 2021-01-14 10:00:00 2021-01-16 10:00:00 125 125 3 2375
3 2021-01-17 10:00:00 2021-01-23 10:00:00 200 200 7 2375
4 2021-01-14 10:00:00 2021-01-19 10:00:00 50 50 6 2375
 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 SUM(t.quantity) AS the_sum, t.dt_start AS dat FROM timetable t GROUP BY dt_start;
the_sum dat
275 2021-01-14 10:00:00
275 2021-01-15 10:00:00
275 2021-01-16 10:00:00
250 2021-01-17 10:00:00
250 2021-01-18 10:00:00
250 2021-01-19 10:00:00
200 2021-01-20 10:00:00
200 2021-01-21 10:00:00
200 2021-01-22 10:00:00
200 2021-01-23 10:00:00
 hidden batch(es)


SELECT c.dt AS "The date:", COALESCE(t1.the_sum, 0) AS "No. delivered" 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: No. delivered
2021-01-12 0
2021-01-13 0
2021-01-14 275
2021-01-15 275
2021-01-16 275
2021-01-17 250
2021-01-18 250
2021-01-19 250
2021-01-20 200
2021-01-21 200
2021-01-22 200
2021-01-23 200
2021-01-24 0
2021-01-25 0
2021-01-26 0
2021-01-27 0
2021-01-28 0
 hidden batch(es)