PostgreSQL Join – Left Outer Join Not Returning All Rows in a Grouping Query

join;postgresql

I'm trying to query all users to find the price of orders in the last month. I'm doing this on PostgreSQL. Here's a toy database to show my problem: I have a people table and an orders table. I'm trying to find the sum of orders for all people in the last month.

Here's my data:

select * from people;
 id |     name     
----+--------------
  1 | bobby lee
  2 | greg grouper
  3 | Hilldawg Ca
(3 rows)

select * from orders;
 id | person_id |    date    | price 
----+-----------+------------+-------
  1 |         3 | 2014-08-20 |  3.50
  2 |         3 | 2014-09-20 |  6.00
  3 |         3 | 2014-09-10 |  7.00
  4 |         2 | 2014-09-10 |  7.00
  5 |         2 | 2014-08-10 |  2.50

And here's the query I'm trying to write: 

SELECT p.id, p.name, sum(o.price)
FROM people p
LEFT OUTER JOIN orders o
ON p.id = o.person_id
WHERE o.date between date '2014-09-01' AND date '2014-09-30'
GROUP BY p.id;

Here is the return I get:

 id |     name     |  sum  
----+--------------+-------
  2 | greg grouper |  7.00
  3 | Hilldawg Ca  | 13.00
(2 rows)

I'm expecting a third row, with person id 1 (bobby lee) having a sum of zero for the last month's orders (since no orders exist for bobby). What am I doing incorrectly with my query? My impression was that doing a left outer join and grouping by people.id means that the people table should set the number of rows in the output.

Best Answer

The WHERE o.date ... condition makes the outer join behave like an inner join, cutting out any rows of peoplethat don't have at least one matching row in orders.

You'll have to move the condition about o.date from the WHERE clause to the joining ON:

SELECT p.id, p.name, coalesce(sum(o.price), 0.00) AS total
FROM people p
  LEFT OUTER JOIN orders o
    ON  p.id = o.person_id
    AND o.date BETWEEN date '2014-09-01' AND date '2014-09-30'
GROUP BY p.id;

Test in: SQLFiddle

Related Question