SELECT COUNT(*) - return 0 along with grouped fields if there are no matching rows

Question

I have the following query:

SELECT employee,department,count(*) AS sum FROM items 
WHERE ((employee = 1 AND department = 2) OR 
      (employee = 3 AND department = 4) OR 
      (employee = 5 AND department = 6) OR 
      ([more conditions with the same structure]))
      AND available = true
GROUP BY employee, department;

If there are no items for a pair "employee-department", then the query returns nothing. I'd like it to return zero instead:

 employee | department | sum 
 ---------+------------+--------
 1        |          2 |      0
 3        |          4 |     12  
 5        |          6 |   1234   

EDIT1

Looks like this is not possible, as Matthew PK explains in his answer to a similar question. I was mistakenly assuming Postgres could extract missing values from WHERE clause somehow.

EDIT2

It is possible with some skills. :) Thanks to Erwin Brandstetter!

Answer 1

Not possible? Challenge accepted. :)

WITH x(employee, department) AS (
   VALUES
    (1::int, 2::int)
   ,(3, 4)
   ,(5, 6)
    -- ... more combinations
   )
SELECT x.employee, x.department, count(i.employee) AS ct
FROM   x
LEFT   JOIN items i ON i.employee = x.employee
                   AND i.department = x.department
                   AND i.available
GROUP  BY x.employee, x.department;

This will give you exactly what you are asking for. If employee and department aren't integer, cast to the matching type.

Per comment from @ypercube: count() needs to be on a non-null column of items, so we get 0 for non-existing critera, not 1.

Also, pull up additional criteria into the LEFT JOIN condition (i.available in this case), so you don't exclude non-existing criteria.

Performance

Addressing additional question in comment.
This should perform very well. With longer lists of criteria, (LEFT) JOIN is probably the fastest method.

If you need it as fast as possible, be sure to create a multicolumn index like:

CREATE INDEX items_some_name_idx ON items (employee, department);

If (employee, department) should be the PRIMARY KEY or you should have a UNIQUE constraint on the two columns, that would do the trick, too.