How do I get the count/sum of the rows (COUNT () or SUM ()) based on another column (of the Type: weekly or yearly)? I have two tables:
- Stores:
| Id | Name | Type |
|---|---|---|
| 1 | Store 1 | Weekly |
| 2 | Store 2 | Yearly |
| 3 | Store 3 | Weekly |
| 4 | Store 4 | Weekly |
- Orders:
| Id | StoreId | OrderDate | Qty |
|---|---|---|---|
| 1 | 1 | 2022-01-31 | 2 |
| 2 | 1 | 2022-12-31 | 5* |
| 3 | 2 | 2022-01-28 | 30* |
| 4 | 2 | 2022-06-30 | 50* |
| 5 | 2 | 2022-12-31 | 70* |
| 6 | 3 | 2022-06-15 | 8 |
| 7 | 3 | 2022-12-27 | 9* |
| 8 | 3 | 2022-12-31 | 3* |
a) If I pass the date range (by weekly,2022-12-26 ~ 2023-01-01), the expected result should look like this:
| Id | Name | Count of orders | Total Qty |
|---|---|---|---|
| 1 | Store 1 | 1 | 5 |
| 2 | Store 2 | 3 | 150 (sum by the year when the store's type equals "Yearly": 30 50 70) |
| 3 | Store 3 | 2 | 12 (sum by the selected week: 9 3) |
| 4 | Store 4 | 0 | 0 |
If the Store type is Yearly then all orders will be summed up based on StoreId & year of OrderDate, if Weekly then based on StoreId & selected OrderDate.
b) I tried using CASE in SELECT statement, but no luck, here are part of my codes:
SELECT s.Id,
s.Name,
COUNT(o.Id) AS 'Count of orders',
sum(o.Qty) AS 'Total Qty'
FROM Stores AS s
LEFT JOIN Orders AS o
ON o.StoreId = s.id
AND (OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01')
GROUP BY s.Id, OrderDate
ORDER BY OrderDate DESC
CodePudding user response：
You could use conditional aggregation as the following:
SELECT s.Id,
s.Name,
COUNT(CASE
WHEN s.Type = 'Yearly' THEN
o.Id
ELSE
CASE
WHEN OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01' THEN
o.Id
END
END) As 'Count of orders',
SUM(CASE
WHEN s.Type = 'Yearly' THEN
o.Qty
ELSE
CASE
WHEN OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01' THEN
o.Qty
ELSE
0
END
END) AS 'Total Qty'
FROM Stores AS s
LEFT JOIN Orders AS o
ON o.StoreId = s.id
GROUP BY s.Id, s.Name
ORDER BY MAX(OrderDate) DESC
See demo.
CodePudding user response：
You can do in this way.
Please take note that, type is a keyword in MySQL.
SELECT s.id,
s.name,
s.type,
COUNT(s.name) AS total_count,
SUM(o.qty) AS total_qty
FROM stores s
LEFT JOIN orders o
ON s.id = o.storeid
WHERE (o.orderdate >= '2022-12-26' AND o.orderDate <= '2023-01-01'
AND s.type = 'Weekly')
OR s.type = 'Yearly'
GROUP BY s.id, s.name, s.type
CodePudding user response：
From the description, calculate count(Orders.Id) and sum(Orders.Qty)
Stores.Type = 'Weekly': Orders.OrderDate between @start_date and @end_date
Stores.Type = 'Yearly': Orders.OrderDate in the year of @start_date (...all orders will be summed up based on StoreId & year of OrderDate.)
Thus, the first step is to have where clause to filter out Orders and then aggregate to Store.Id level. Then, 2nd step is to left join from Stores table to the result of first step so that stores without sales in specified date ranges are reported.
set @start_date = '2022-12-26', @end_date = '2023-01-01';
with cte_store_sales as (
select s.Id,
count(o.Id) as order_count,
sum(o.Qty) as total_qty
from stores s
left
join orders o
on s.Id = o.StoreId
where (s.type = 'Weekly' and o.OrderDate between @start_date and @end_date)
or (s.type = 'Yearly' and o.OrderDate between makedate(year(@start_date),1)
and date_sub(date_add(makedate(year(@start_date),1), interval 1 year), interval 1 day))
group by s.Id)
select s.Id,
s.Name,
coalesce(ss.order_count, 0) as "Count of Orders",
coalesce(ss.total_qty, 0) as "Total Qty"
from stores s
left
join cte_store_sales ss
on s.Id = ss.Id
order by s.Id;
Output:
Id|Name |Count of Orders|Total Qty|
-- ------- --------------- ---------
1|Store 1| 1| 5|
2|Store 2| 3| 150| <-- Store sales in year 2022
3|Store 3| 2| 12|
4|Store 4| 0| 0| <-- Report stores without sales
CodePudding user response：
First of all, we shall extract the raw data matching the orderdate table condition, which can be used for the sake of aggregation later. Note,here I treat the date range as inclusive. Therefore, it shall be year 2022 and 2023 for 2022-12-26 ~ 2023-01-01 if the type is yearly.
select s.id id, name,
(case when type='weekly' and orderdate between '2022-12-26' and '2023-01-01' then qty
when type='yearly' and year(orderdate) between year('2022-12-26') and year('2023-01-01') then qty
end) as qt
from Stores s
left join Orders o
on s.id=o.storeid;
-- result set:
# id, name, qt
1, Store 1, 5
2, Store 2, 30
2, Store 2, 50
2, Store 2, 70
3, Store 3,
3, Store 3, 9
3, Store 3, 3
4, Store 4,
The rest is to do the summarisation job using the derived table. Note: Since the column name is not in the group by list, but it's actually unique for a specific storeid, we can use the any_value function to bypass the restriction which might be enforced due to the SQL_MODE system variable.
select id,any_value(name) as'Name',count(qt) as 'Count of orders', ifnull(sum(qt),0) as 'Total Qty'
from
(select s.id id, name,
(case when type='weekly' and orderdate between '2022-12-26' and '2023-01-01' then qty
when type='yearly' and year(orderdate) between year('2022-12-26') and year('2023-01-01') then qty
end) as qt
from Stores s
left join Orders o
on s.id=o.storeid) tb
group by id
order by id
;
-- result set:
# id, Name, Count of orders, Total Qty
1, Store 1, 1, 5
2, Store 2, 3, 150
3, Store 3, 2, 12
4, Store 4, 0, 0