1479. Sales by Day of the Week----case when条件检查

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    | 
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(ordered_id, item_id) is the primary key for this table.
This table contains information of the orders placed.
order_date is the date when item_id was ordered by the customer with id customer_id.

 

Table: Items

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id is the primary key for this table.
item_name is the name of the item.
item_category is the category of the item.

 

You are the business owner and would like to obtain a sales report for category items and day of the week.

Write an SQL query to report how many units in each category have been ordered on each day of the week.

Return the result table ordered by category.

The query result format is in the following example:

 

Orders table:
+------------+--------------+-------------+--------------+-------------+
| order_id   | customer_id  | order_date  | item_id      | quantity    |
+------------+--------------+-------------+--------------+-------------+
| 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           |
+------------+--------------+-------------+--------------+-------------+

Items table:
+------------+----------------+---------------+
| 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       |
+------------+----------------+---------------+

Result table:
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 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         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
On Monday (2020-06-01, 2020-06-08) were sold a total of 20 units (10 + 10) in the category Book (ids: 1, 2).
On Tuesday (2020-06-02) were sold a total of 5 units  in the category Book (ids: 1, 2).
On Wednesday (2020-06-03) were sold a total of 5 units in the category Phone (ids: 3, 4).
On Thursday (2020-06-04) were sold a total of 1 unit in the category Phone (ids: 3, 4).
On Friday (2020-06-05) were sold 10 units in the category Book (ids: 1, 2) and 5 units in Glasses (ids: 5).
On Saturday there are no items sold.
On Sunday (2020-06-14, 2020-06-21) were sold a total of 10 units (5 +5) in the category Phone (ids: 3, 4).
There are no sales of T-Shirt.

解题思路：

1、将购买日期换成星期名称，使用函数dayname（）

2、统计不同category在不同星期日期的购买数量之和，没有买的商品也统计在内

SELECT DAYNAME(order_date) AS week_day, SUM(quantity) AS sum_quan, item_category  
FROM orders o RIGHT JOIN items i
ON o.item_id = i.item_id
GROUP BY DAYNAME(order_date), item_category

3、通过case when函数，将星期日期作为新的字段名称，并统计该字段对应的购买数量

SELECT item_category AS category,
SUM(CASE WHEN week_day = 'Monday' THEN sum_quan ELSE 0 END) 'Monday',
SUM(CASE WHEN week_day = 'Tuesday' THEN sum_quan ELSE 0 END) AS 'Tuesday',
SUM(CASE WHEN week_day = 'Wednesday' THEN sum_quan ELSE 0 END) AS 'Wednesday',
SUM(CASE WHEN week_day = 'Thursday' THEN sum_quan ELSE 0 END) AS 'Thursday',
SUM(CASE WHEN week_day = 'Friday' THEN sum_quan ELSE 0 END) AS 'Friday',
SUM(CASE WHEN week_day = 'Saturday' THEN sum_quan ELSE 0 END) AS 'Saturday',
SUM(CASE WHEN week_day = 'Sunday' THEN sum_quan ELSE 0 END) AS 'Sunday' 

4、综合以上步骤

SELECT item_category AS category,
SUM(CASE WHEN week_day = 'Monday' THEN sum_quan ELSE 0 END) 'Monday',
SUM(CASE WHEN week_day = 'Tuesday' THEN sum_quan ELSE 0 END) AS 'Tuesday',
SUM(CASE WHEN week_day = 'Wednesday' THEN sum_quan ELSE 0 END) AS 'Wednesday',
SUM(CASE WHEN week_day = 'Thursday' THEN sum_quan ELSE 0 END) AS 'Thursday',
SUM(CASE WHEN week_day = 'Friday' THEN sum_quan ELSE 0 END) AS 'Friday',
SUM(CASE WHEN week_day = 'Saturday' THEN sum_quan ELSE 0 END) AS 'Saturday',
SUM(CASE WHEN week_day = 'Sunday' THEN sum_quan ELSE 0 END) AS 'Sunday' 
FROM 
(SELECT DAYNAME(order_date) AS week_day, SUM(quantity) AS sum_quan, item_category  
FROM orders o RIGHT JOIN items i
ON o.item_id = i.item_id
GROUP BY DAYNAME(order_date), item_category) t
GROUP BY item_category
ORDER BY item_category;