聚合和重命名的 SQLY 查询错误：在"SELECT "之后发现意外的令牌"age, SUM(BALANCE) AS TOTALBALANCE, DISTINCT COUNT(id"

Question

SELECT age, SUM(BALANCE) AS TOTALBALANCE, DISTINCT COUNT(id) AS ids 
FROM p1.account 
JOIN p1.customer ON p1.customer.id=p1.account.id 
HAVING (age>=%s AND age<=%s)

我得到这个错误：

一个意外的令牌"年龄，总额(余额(作为总额，差异在"SELECT"之后找到了COUNT(id(。预期的令牌可能包括："quot；。。SQLCODE=-104，SQLSTATE=42601，DRIVER=4.26.14

我的查询不起作用的任何原因。不太确定我的查询字符串出了什么问题

Answer 1

它不起作用，因为您错过了GROUP BY子句

尝试

SELECT age, SUM(BALANCE) AS TOTALBALANCE, COUNT(DISTINCT id) AS ids 
FROM p1.account JOIN p1.customer ON p1.customer.id=p1.account.id 
GROUP BY age 
HAVING (age>=%s AND age<=%s)

Answer 2

根据您的评论(这应该是个问题(：

我试图找到某个年龄段内用户的平均总余额(用户可能有多个银行账户，因此我需要获得唯一用户id的数量(。

这表明有两个级别的聚合。第一个是获得每个客户的总余额。我认为这就是逻辑：

select c.id, sum(balance) as customer_balance
from  p1.account a join
p1.customer c
on c.id = a.id
where age >= %s and age <= %s
group by c.id;

第二个是获得平均余额：

select avg(customer_balance)
from (select c.id, sum(balance) as customer_balance
from  p1.account a join
p1.customer c
on c.id = a.id
where age >= %s and age <= %s
group by c.id
) c;

您实际上并不需要两个级别的聚合，但这是解释逻辑的最简单方法。这应该做同样的事情：

select sum(a.balance) / count(distinct c.id) as customer_balance
from  p1.account a join
p1.customer c
on c.id = a.id
where age >= %s and age <= %s;

请注意，在任何一种情况下，如果这是您真正想要的，则可以group by age。不过，你的问题表明，你希望所有的年龄都结合起来。