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.