根据另一列在SQL中获取记录的COUNT/SUM。

Question

我如何基于另一列（类型为：weekly或yearly）获取行的计数/总和（COUNT()或SUM()）？我有两个表：

1. 店铺：

Id	Name	Type
1	Store 1	Weekly
2	Store 2	Yearly
3	Store 3	Weekly
4	Store 4	Weekly

2. 订单：

编号	商店编号	订单日期	数量
1	1	2022年1月31日	2
2	1	2022年12月31日	5*
3	2	2022年1月28日	30*
4	2	2022年6月30日	50*
5	2	2022年12月31日	70*
6	3	2022年6月15日	8
7	3	2022年12月27日	9*
8	3	2022年12月31日	3*

a) 如果我传递日期范围（按周计算，2022-12-26 ~ 2023-01-01），期望的结果应该如下：

Id	Name	订单数量	总数量
1	商店 1	1	5
2	商店 2	3	150（当商店类型为“年度”时按年份求和：30+50+70）
3	商店 3	2	12（按所选周求和：9+3）
4	商店 4	0	0

如果 Store type 是 Yearly，则所有订单将根据 StoreId、OrderDate 的&year进行汇总；如果是Weekly，则基于 StoreId 和选择的 OrderDate 进行汇总。

b) 我尝试在 SELECT 语句中使用 CASE，但没有成功，以下是我的部分代码：

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

- Jack

Answer 1

您可以使用以下条件聚合：

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

请查看演示。

- ahmed

---

这个似乎可以工作！！！让我试试！;) - Jack

在我的情况下，我使用派生表来避免获取订单数量的麻烦。否则，为了count函数，将再次使用相同的复杂查询来获取数量。 - blabla_bingo

是的！这是我用来获得期望结果的最终方法！抱歉回复晚了，因为很多临时项目跳过了队列，现在我只能再次回到这个话题。 ;) - Jack

Answer 2

你可以这样做。 请注意，type 是 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

- learning

---

如果使用这种方式，由于订单表中没有记录，因此不会显示第4个商店。 - Jack

Answer 3

根据描述，计算count(Orders.Id)和sum(Orders.Qty)

1. Stores.Type = 'Weekly': Orders.OrderDate在@start_date和@end_date之间

2. Stores.Type = 'Yearly': Orders.OrderDate在@start_date所在的年份内（...所有订单将基于StoreId和OrderDate的年份进行汇总。）

因此，第一步是使用where子句过滤订单并聚合到Store.Id级别。然后，第二步是从Stores表左连接到第一步的结果，以便报告指定日期范围内没有销售的商店。

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;

输出：

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 

- JHH

---

谢谢。我正在尝试避免使用子查询或CTE，这次只在复杂查询中使用CTE。但我认为这次可以用更简单的方法解决，不知怎么就卡住了。^^!!!但如果没有成功，我一定会尝试这个方法的。 - Jack

Answer 4

首先，我们将提取与orderdate表条件匹配的原始数据，以便稍后进行聚合。请注意，这里我将日期范围视为包含。因此，如果类型是年度，则应该是2022年和2023年，对应于2022-12-26 ~ 2023-01-01。

    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, 

接下来要做的就是使用派生表进行汇总。注意：由于列 name 不在 group by 列表中，但实际上对于特定的storeid它是唯一的，因此我们可以使用any_value函数来绕过可能由 SQL_MODE 系统变量强制执行的限制。

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

- blabla_bingo