sql - PostgresSQL 'WHERE' 应该去哪里聚合？

Question

以下 postgres 查询有效:

dvdrental=# SELECT title, COUNT(title) AS c FROM film          
JOIN inventory
ON film.film_id = inventory.film_id GROUP BY film.title LIMIT 5;

      title       | c 
------------------+---
 Academy Dinosaur | 8
 Ace Goldfinger   | 3
 Adaptation Holes | 4
 Affair Prejudice | 7
 African Egg      | 3
(5 rows)

Time: 1.671 ms

然后，如果我想限制库存中具有特定拷贝数量的电影或计算库存中有特定数量的电影数量，起点是:

dvdrental=# SELECT title, COUNT(title) AS c FROM film
JOIN inventory
ON film.film_id = inventory.film_id GROUP BY film.title WHERE c = 4;
ERROR:  syntax error at or near "WHERE"
LINE 3: ...m.film_id = inventory.film_id GROUP BY film.title WHERE c = ...
                                                             ^
Time: 0.337 ms

..但它不起作用。

我已经为 WHERE c = 4 尝试了所有可能的位置，但都行不通。有一种感觉，如果它是 MySQL 而不是 PostgresSQL，它会起作用。

有人知道怎么解决吗？

Answer 1

它将是 having 而不是 where

    SELECT title, COUNT(title) AS c FROM film
    JOIN inventory
    ON film.film_id = inventory.film_id     
    GROUP BY film.title 
    having COUNT(title)=4

用于聚合函数的HAVING子句