Alex Rivera | Logout

PostgreSQL WHERE count condition

Asked 2011-11-14T09:26:16.000
63

I have following query in PostgreSQL:

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;

Why do I get this error:

ERROR: column "overall_count" does not exist

My table structure:

License(license_id, license_name, limit_call, create_date, expire_date)
Log(log_id, license_id, log, call_date)

I want to check if a license has reached the limit for calls in a specific month.

sql postgresql group-by having
Edit
Report

1 Answer

68
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  -- , a.limit_call  -- add in old versions
HAVING a.limit_call > count(b.license_id)

Since Postgres 9.1 the primary key covers all columns of a table in the GROUP BY clause. In older versions you'd have to add a.limit_call to the GROUP BY list. The release notes for 9.1:

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause

Further reading:

The condition you had in the WHERE clause has to move to the HAVING clause since it refers to the result of an aggregate function (after WHERE has been applied). And you cannot refer to output columns (column aliases) in the HAVING clause, where you can only reference input columns. So you have to repeat the expression. The manual:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

I reversed the order of tables in the FROM clause and cleaned up the syntax a b

answered 2011-11-14T09:54:02.170

Your Answer