MySQL:计算新列(将天转换为周)以获得产品的每周销售额

Question

我在 mysql 中的表 sales 如下所示:

ID             Date     Quantity
00000001    01/01/2017  5
00000002    02/01/2017  25
00000003    01/01/2017  2
00000001    04/01/2017  58
00000005    01/01/2017  8
00000001    10/01/2017  2

我想获取每种产品的每周销售额(本例中为 week1)。以下是我的查询

SELECT ID, 
    IF (YEARWEEK(Date) = '201401', SUM(Quantity), NULL) AS WEEK1
FROM sales
GROUP BY ID

它返回以下错误:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sales.registerDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我也使用过 CASE 语句，但它对我来说效果不佳。

SELECT ID, 
    CASE WHEN  (YEARWEEK(Date) = '201401' THEN SUM(Quantity) END AS WEEK1
FROM sales
GROUP BY ID

返回错误

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN SUM(Quantity) END) WEEK1
FROM sales
GROUP BY ID' at line 2

预期结果格式为

   ID     week1
00000001    63
00000002    25
00000003    2
00000005    8

获得所需结果的正确方法是什么？

Answer 1

尝试总结 case 语句，例如:

SELECT ID, 
    SUM(CASE WHEN YEARWEEK(Date) = '201401' THEN Quantity END) AS WEEK1
FROM sales
GROUP BY ID