如何在多列上基于SELECT DISTINCT进行求和

Question

我有一个表，其中有3个数字列(a,b,c)和两个分类列(x,y)。我想为a ,b and c求和，但基于每个唯一x and y组合。我该怎么做？我已经尝试了以下代码

SELECT   
SUM(CAST(t.a as INT64)) as a_,
SUM(CAST(t.b  as INT64)) as b_ ,
SUM(CAST(t.c  as INT64)) as c_,
FROM (
SELECT DISTINCT x ,y FROM <table_name>  
) t

现在我得到的是在t中找不到a

我该怎么解决？我做错了什么？

Answer 1

在这种情况下，您需要使用

SELECT   
x,
y,
SUM(CAST(t.a as INT64)) as a_,
SUM(CAST(t.b  as INT64)) as b_ ,
SUM(CAST(t.c  as INT64)) as c_
from
<table_name>  
group by x, y

Answer 2

如果值已经是数字，为什么要强制转换？我想你想要：

SELECT x, y, SUM(a) as sum_a, SUM(b) as sum_b, SUM(c) as sum_c
FROM t
GROUP BY x, y;

如果你想要这三个的总和(你的问题表明，那么你想要：

SUM(a + b + c) as sum_abc,

或：

SUM(a) + SUM(b) + SUM(c) as sum_abc

如果其中任何一个值是NULL，则后两个值有细微的不同。目前还不清楚你可能想要哪一个。