3

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.

5

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

  • Thanks, this was definitely helpfuk. I'm beefing up on SQL for an interview where it's an ancillary but useful skill. This was helpful and I'll definitely not make this mistake again. Outer joins are always a bit tougher than inner joins to conceptualize. – user1276560 Oct 13 '14 at 2:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.