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.
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 theGROUP 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. Per documentation:
An output column's name can be used to refer to the column's value in
ORDER BY
andGROUP BY
clauses, but not in theWHERE
orHAVING
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 bit to make it less confusing. USING
is just a notational convenience here.
I used LEFT JOIN
instead of JOIN
, so you do not exclude licenses without any logs at all.
Only non-null values are counted by count()
. Since you want to count related entries in table "Log"
it is safer and slightly cheaper to use count(b.license_id)
. This column is used in the join, so we don't have to bother whether the column can be null or not.
count(*)
is even shorter and slightly faster, yet. If you don't mind to get a count of 1
for 0
rows in the left table, use that.
Aside: I would advise not to use mixed case identifiers in Postgres if possible. Very error prone.