PostgreSQL错误中WHERE子句中不允许的聚合 [英] Aggregates not allowed in WHERE clause in postgreSQL error
本文介绍了PostgreSQL错误中WHERE子句中不允许的聚合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试执行以下查询,并且我是编写 SQL
查询的初学者,想知道如何在<$中执行以下聚合函数功能c $ c> WHERE 子句。对我来说,任何帮助都是一条很好的学习曲线。
I was trying to execute this following query, and I am a beginner in writing SQL
Queries, was wondering how can I achieve the following aggregate functions functionality doing in WHERE
clause. Any help on this would be a great learning curve for me.
select a.name,a.add,a.mobile from user a
INNER JOIN user_info aac
ON aac.userid= a.userid
INNER JOIN info ac
ON aac.infoid= ac.infoid
WHERE a.total < 8* AVG(ac.total)
GROUP BY a.name, a.add, a.mobile;
这是我在PostgreSQL中遇到的错误:
And this is the error I am getting in PostgreSQL:
ERROR: aggregates not allowed in WHERE clause
LINE 1: ...infoid = ac.infoid where a.total < 8* AVG(ac.tot...
^
********** Error **********
ERROR: aggregates not allowed in WHERE clause
SQL state: 42803
Character: 190
我想使用 Having
子句获得结果吗?对此进行任何更正都会有很大帮助!
Am I suppose to use Having
clause to have the results? any correction on this would be a great help!
推荐答案
您可以在子查询中使用窗口函数来做到这一点:
You can do this with a window function in a subquery:
select name, add, mobile
from (select a.name, a.add, a.mobile, total,
avg(ac.total) over (partition by a.name, a.add, a.mobile) as avgtotal, a.total
from user a INNER JOIN
user_info aac
ON aac.userid= a.userid INNER JOIN
info ac
ON aac.infoid= ac.infoid
) t
WHERE total < 8 * avgtotal
GROUP BY name, add, mobile;
这篇关于PostgreSQL错误中WHERE子句中不允许的聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文