在哪里使用聚合函数 [英] Using an aggregate function in where

问题描述

我在这里看到了几个类似问题的话题，但是我找不到适合自己的需求。

I saw a few threads here with similar questions however I cloudn't find something fitting my needs.

请考虑以下表格和示例数据：

Consider the following table and exapmle data:

CREATE TABLE foo(a int, b int);

INSERT INTO FOO VALUES(1, 3);
INSERT INTO FOO VALUES(1, 3);
INSERT INTO FOO VALUES(1, 4);
INSERT INTO FOO VALUES(2, 5);
INSERT INTO FOO VALUES(2, 3);
INSERT INTO FOO VALUES(3, 10);

请考虑以下查询：

SELECT a,
       sum(b)
FROM foo
GROUP BY a;

工作正常。我想更改该查询，以使其仅匹配总和大于9的组。我的（失败）尝试是：

It works fine. I want to alter that query so that it will only match groups where the sum is bigger than 9. My (failed) attempt is:

SELECT a,
       SUM(b)
FROM foo
WHERE SUM(b) >9
GROUP BY a;

在postgres中正确的做法是什么？

What is the correct way to do it in postgres ?

推荐答案

您不能在 where 子句中使用聚合表达式-这就是具有子句用于：

You can't use aggregate expression in the where clause - this is what the having clause is for:

SELECT   a, SUM(b)
FROM     foo
GROUP BY a
HAVING   SUM(b) > 9

这篇关于在哪里使用聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！