How to Correctly Count Rows in PostgreSQL Using WHERE and Aggregate Functions?

PostgreSQL WHERE count condition

In PostgreSQL, the following query returns an error:

SELECT 
    COUNT(a.log_id) AS overall_count
FROM 
    "Log" as a, 
    "License" as b 
WHERE 
    a.license_id=7 
AND 
    a.license_id=b.license_id 
AND
    b.limit_call > overall_count
GROUP BY 
    a.license_id;

ERROR:  column "overall_count" does not exist

The reason for this error is that the WHERE clause references an output column, "overall_count", which is not yet defined in the table structure. To fix this, we must move the count condition to the HAVING clause since it refers to an aggregate function result after WHERE has been applied.

Furthermore, the query should also use a LEFT JOIN instead of a regular JOIN to avoid excluding licenses without any logs and use count(b.license_id) or count(*) for efficiency and clarity.

The correct query should look like this:

SELECT a.license_id, a.limit_call
     , count(b.license_id) AS overall_count
FROM   "License"  a
LEFT   JOIN "Log" b USING (license_id)
WHERE  a.license_id = 7 
GROUP  BY a.license_id 
HAVING a.limit_call > count(b.license_id)

The above is the detailed content of How to Correctly Count Rows in PostgreSQL Using WHERE and Aggregate Functions?. For more information, please follow other related articles on the PHP Chinese website!