I have the following tables:
Orders:
|order_id|customer_id| order_date |item_id|quantity|
|--------|-----------|----------------------|-------|--------|
| 1 | 1 | 2020-06-01 00:00:00 | 1 | 10|
| 2 | 1 | 2020-06-08 00:00:00 | 2 | 10|
| 3 | 2 | 2020-06-02 00:00:00 | 1 | 5 |
| 4 | 3 | 2020-06-03 00:00:00 | 3 | 5 |
| 5 | 4 | 2020-06-04 00:00:00 | 4 | 1 |
| 6 | 4 | 2020-06-05 00:00:00 | 5 | 5 |
| 7 | 5 | 2020-06-05 00:00:00 | 1 | 10|
| 8 | 5 | 2020-06-14 00:00:00 | 4 | 5 |
| 9 | 5 | 2020-06-21 00:00:00 | 3 | 5 |
| 5 | 4 | 2020-06-08 00:00:00 | 4 | 1 |
Items:
|item_id|item_name |item_category|
|-------|--------- --------|-------------|
| 1 |LC Alg. Book |Book |
| 2 |LC DB. Book |Book |
| 3 |LC SmarthPhone |Phone |
| 4 |LC Phone 2020 |Phone |
| 5 |LC SmartGlass |Glasses |
| 6 |LC T-Shirt XL |T-Shirt |
Im creating a query that should return a report on how many units in each item_category have been ordered on each day of the week. The report should look like:
------------ ----------- ----------- ----------- ----------- ----------- ----------- -----------
| Category | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
------------ ----------- ----------- ----------- ----------- ----------- ----------- -----------
| Book | 20 | 5 | 0 | 0 | 10 | 0 | 0 |
| Glasses | 0 | 0 | 0 | 0 | 5 | 0 | 0 |
| Phone | 0 | 0 | 5 | 1 | 0 | 0 | 10 |
| T-Shirt | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
------------ ----------- ----------- ----------- ----------- ----------- ----------- -----------
Following is my query, which is accurate for all but one case - 'Phone' on Thursday should be qty = 1 but my query returns 0. This quantity is returned accurately in cte2, but I loose it in cte3. Can't figure out why. What's wrong with my query?
with cte1 as
(select * from
(select "Monday" as day_name union
select "Tuesday" union
select "Wednesday" union
select "Thursday " union
select "Friday" union
select "Saturday " union
select "Sunday" ) t1
cross join
(select distinct item_category from items) t2),
cte2 as
(select i.item_category,
dayname(order_date) as day_name,
sum(quantity) as tt_qty
from orders o
inner join items i
on i.item_id = o.item_id
group by i.item_category, dayname(order_date)),
cte3 as
(select cte1.day_name,
cte1.item_category,
coalesce(cte2.tt_qty, 0) as tt_qty
from cte1
left join cte2
on cte2.day_name = cte1.day_name
and cte2.item_category = cte1.item_category)
select item_category as category,
coalesce (max(case when cte3.day_name ='Monday' then cte3.tt_qty
end),0) as Monday,
coalesce (max(case when cte3.day_name ='Tuesday' then cte3.tt_qty
end),0) as Tuesday,
coalesce (max(case when cte3.day_name ='Wednesday' then cte3.tt_qty
end),0) as Wednesday,
coalesce (max(case when cte3.day_name ='Thursday' then cte3.tt_qty
end),0) as Thursday,
coalesce (max(case when cte3.day_name ='Friday' then cte3.tt_qty
end),0) as Friday,
coalesce (max(case when cte3.day_name ='Saturday' then cte3.tt_qty
end),0) as Saturday,
coalesce (max(case when cte3.day_name ='Sunday' then cte3.tt_qty
end),0) as Sunday
from cte3
group by item_category
order by 1
DBfiddle link: https://www.db-fiddle.com/#&togetherjs=sD7fqL9voK
CodePudding user response：
You have a space after the "y" in Thursday and Saturday of cte1, hence the join isn't working as expected and the coalesce is returning zero for all Thursdays/Saturdays. The following replacement of cte1 will make the query work as expected.
with cte1 as
(select * from
(select 1 as wd, "Monday" as day_name union all
select 2, "Tuesday" union all
select 3, "Wednesday" union all
select 4, "Thursday" union all ## you had a space after "Thursday "
select 5, "Friday" union all
select 6, "Saturday" union all ## you had a space after "Saturday "
select 7, "Sunday" ) t1
cross join
(select distinct item_category from items) t2),
see: dbfiddle.uk here
I introduced an integer for the weekdays as it made ordering easier, also used union all instead of just union
CodePudding user response：
With mySQL 8.0 my solution in just 3 SELECTs and 1 JOIN:
CREATE TABLE orders_tab
(order_id INT, customer_id INT, order_date DATE, item_id INT, qty INT);
INSERT INTO orders_tab VALUES
(1, 1, '2020-06-01', 1, 10),
(2, 1, '2020-06-08', 2, 10),
(3, 2, '2020-06-02', 1, 5),
(4, 3, '2020-06-03', 3, 5),
(5, 4, '2020-06-04', 4, 1),
(6, 4, '2020-06-05', 5, 5),
(7, 5, '2020-06-05', 1, 10),
(8, 5, '2020-06-14', 4, 5),
(9, 5, '2020-06-21', 3, 5),
(5, 4, '2020-06-08', 4, 1);
CREATE TABLE items_tab
(item_id INT, item_name VARCHAR(22), item_cat VARCHAR(9));
INSERT INTO items_tab VALUES
(1, 'LC Alg. Book', 'Book'),
(2, 'LC DB. Book', 'Book'),
(3, 'LC SmarthPhone', 'Phone'),
(4, 'LC Phone 2020', 'Phone'),
(5, 'LC SmartGlass', 'Glasses'),
(6, 'LC T-Shirt XL', 'T-Shirt');
/*___________________________________*/
WITH cte AS (
SELECT item_cat, WEEKDAY(order_date) AS week_day, OT.item_id, SUM(qty) AS items_qty_by_day
FROM orders_tab OT
RIGHT JOIN items_tab IT ON (OT.item_id=IT.item_id)
GROUP BY week_day, OT.item_id
), cte2 AS (
SELECT week_day, item_cat, SUM(items_qty_by_day) AS items_qty_by_day
FROM cte
GROUP BY week_day, item_cat
)
SELECT item_cat,
SUM(IF(week_day=0, items_qty_by_day, 0)) AS monday,
SUM(IF(week_day=1, items_qty_by_day, 0)) AS tuesday,
SUM(IF(week_day=2, items_qty_by_day, 0)) AS wednesday,
SUM(IF(week_day=3, items_qty_by_day, 0)) AS thursday,
SUM(IF(week_day=4, items_qty_by_day, 0)) AS friday,
SUM(IF(week_day=5, items_qty_by_day, 0)) AS saturday,
SUM(IF(week_day=6, items_qty_by_day, 0)) AS sunday
FROM cte2
GROUP BY item_cat