PostgreSQL Where count condition

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

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  3. SQL query for finding records where count > 1
  4. GROUP BY + CASE statement
  5. Mysql: how to select groups having certain values?
  6. Conditional SQL count
  7. Get values from first and last row per group
  8. Are PostgreSQL column names case-sensitive?
  9. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  10. How to check if a table exists in a given schema
  11. Save PL/pgSQL output from PostgreSQL to a CSV file
  12. How to include “zero” / “0” results in COUNT aggregate?
  13. SQL SELECT speed int vs varchar
  14. delete “column does not exist”
  15. How to create a temporary function in PostgreSQL?
  16. Postgres Case Sensitivity
  17. Polymorphism in SQL database tables?
  18. Record returned from function has columns concatenated
  19. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  20. postgresql: INSERT INTO … (SELECT * …)
  21. PostgreSQL JOIN with array type with array elements order, how to implement?
  22. Trim trailing spaces with PostgreSQL
  23. Grant privileges for a particular database in PostgreSQL
  24. Functions with variable number of input parameters
  25. What is the fastest way to truncate timestamps to 5 minutes in Postgres?
  26. Getting results between two dates in PostgreSQL
  27. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  28. Postgres trigger after insert accessing NEW
  29. Subtract hours from the now() function
  30. SQL Populate table with random data

Leave a Comment