I have this table which lists the delivery days available for an area. Also given are the order date and count of stores with the same order date and delivery day.
CREATE TABLE table1
(`order_date` date, `area` varchar(20), `delivery_days` varchar(20), `stores` int)
;
INSERT INTO table1
(`order_date`, `area`, `delivery_days`, `stores`)
VALUES
('2020-11-22', 'A', '1,3,5', 17),
('2020-11-25', 'A', '1,3,5', 4),
('2020-11-26', 'B', '2,4,6', 9),
('2020-11-28','B', '2,4,6', 6);
You can read the available delivery days as (1 for Mon, 2 for Tue, 3 for Wed... 6 for Sat).
With that said, if stores from area A ordered on 2020-11-22, Sunday, the nearest delivery day is the next day, 1 Monday. And if stores from the same area ordered on 2020-11-25, Wednesday, the nearest delivery day is 2 days from said date, which is 5 Friday.
order_date area delivery_days stores
2020-11-22 A 1,3,5 17
2020-11-25 A 1,3,5 4
2020-11-26 B 2,4,6 9
2020-11-28 B 2,4,6 6
What I would like to do is summarize this information into a table that looks like this-- where each column counts the number of stores that fall into the delivery category then gets the percentage relative to the total count of stores.
area total_stores next_day_delivery percent 2_day_delivery percent 3_day_delivery percent
A 21 17 81 4 19
B 15 9 60 6 40
How can I do this? Here's the sqlfiddle: http://sqlfiddle.com/#!9/784e1/1
