使用左连接函数的Group by Date在结果中显示重复的日期

Question

正在尝试获取3个字段，这些字段使用左连接按日期分组。group by date显示重复的日期。

在MySQL中尝试过，它工作正常，但在BigQuery中不工作。

SELECT DATE(a.transactionDate) as date, 
CASE WHEN b.memberProfileNumber LIKE 'M0%' THEN SUM(a.fromAmount) END AS 
col1,
CASE WHEN b.memberProfileNumber NOT LIKE 'M0%' THEN SUM(a.fromAmount) END 
AS col2
FROM `fashionpoints*` as a
LEFT JOIN `fashionprofile*` as b
ON a.toAccountId = b.id 
WHERE a.fromATC = 'usd' AND
a.type = 'awarding' AND
a.status = 'active'
GROUP BY date

预期输出为DISTINCT date，而实际结果为重复日期。

预期输出：

​

​

实际结果：Actual Result

Answer 1

您的聚合是错误的。尝试替换为SUM(CASE WHEN... THEN...ELSE 0 END) as col1/2