PostgreSQL WHERE count condition

Question

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.

Answer 1

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:

• Why can't I exclude dependent columns from `GROUP BY` when I aggregate by a key?

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 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.

Answer 2

Pure conditional count(*):

  SELECT COUNT(*) FILTER(where a.myfield > 0) AS my_count
    FROM "Log" as a 

GROUP BY a.license_id

so you:

• get 0 for groups where the condition never meets
• can add as many count(*) columns as you need

---

Filter-out the groups, having condition mismatch:

NOTE: you cannot use HAVING b.limit_call > ..., unless you group by limit_call. But you can use an agregate function to map many "limit_calls" in the group into a single value. For example, in your case, you can use MAX:

  SELECT COUNT(a.log_id) AS overall_count
    FROM "Log" as a 
    JOIN "License" b ON(a.license_id=b.license_id)

GROUP BY a.license_id
  HAVING MAX(b.limit_call) > COUNT(a.log_id)

And don't care about duplicating COUNT(a.log_id) expression in the first and in the last lines. Postgres will optimize it.

Answer 3

The where query doesn't recognize your column alias, and furthermore, you're trying to filter out rows after aggregation. Try:

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 
GROUP BY 
a.license_id
having b.limit_call > count(a.log_id);

The having clause is similar to the where clause, except that it deals with columns after an aggregation, whereas the where clause works on columns before an aggregation.

Also, is there a reason why your table names are enclosed in double quotes?