How to include “zero” / “0” results in COUNT aggregate?

by

You want an outer join for this (and you need to use person as the “driving” table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you’ll not get a zero.

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

More Related Contents:

  1. Aggregate columns with additional (distinct) filters
  2. PostgreSQL: running count of rows for a query ‘by minute’
  3. Two SQL LEFT JOINS produce incorrect result
  4. Create a pivot table with PostgreSQL
  5. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  6. Return multiple columns of the same row as JSON array of objects
  7. GROUP BY + CASE statement
  8. Best way to count rows by arbitrary time intervals
  9. Selecting data into a Postgres array
  10. Compute percents from SUM() in the same SELECT sql query
  11. Get the distinct sum of a joined table column
  12. Aggregate a single column in query with many columns
  13. Multiple array_agg() calls in a single query
  14. Postgres window function and group by exception
  15. Count cumulative total in Postgresql
  16. Truncate timestamp to arbitrary intervals
  17. Select rows which are not present in other table
  18. How to implement a many-to-many relationship in PostgreSQL?
  19. Check if value exists in Postgres array
  20. How to use ANY instead of IN in a WHERE clause?
  21. Is there a shortcut for SELECT * FROM?
  22. sql statement error: “column .. does not exist”
  23. Getting all Buildings in range of 5 miles from specified coordinates
  24. Call a set-returning function with an array argument multiple times
  25. Fastest check if row exists in PostgreSQL
  26. Discard millisecond part from timestamp
  27. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  28. SQL column reference “id” is ambiguous
  29. IN Clause with NULL or IS NULL
  30. Postgresql query between date ranges

Leave a Comment