On Jan 17, 3:33 am, t...@xxxxxxxxxxxxxxxxx (Tom Molesworth) wrote: > Hi Nick, > > On 17/01/12 00:18, Nick wrote: > > > > > > > > > > > I have three tables (users, books, pencils) and would like to get a > > list of all users with a count and total price of their books and > > pencils for 2012-01-01... > > > So with this data... > > > users (user_id) > > 1 > > 2 > > 3 > > > books (user_id, price, created) > > 1 | $10 | 2012-01-01 > > 1 | $10 | 2012-01-01 > > 3 | $10 | 2012-01-01 > > > pencils > > 1 | $.50 | 2012-01-02 > > 3 | $.50 | 2012-01-01 > > 3 | $.50 | 2012-01-02 > > > What is the most efficient way to get this result... > > > query_result (user_id, book_count, book_price_total, pencil_count, > > pencil_price_total) > > 1 | 2 | $20 | 0 | $0 > > 2 | 0 | $0 | 0 | $0 > > 3 | 1 | $10 | 1 | $.50 > > Seems straightforward enough - left join the tables, group the result on > user_id - so I'd write it as: > > select u.user_id, count(b.user_id) as "book_count", > coalesce(sum(b.price), 0) as "book_price_total", count(p.user_id) as > "pencil_count", coalesce(sum(b.price), 0) as "pencil_price_total" > from users u > left join books b on b.user_id = u.user_id and b.created = '2012-01-01' > left join pencils p on p.user_id = u.user_id and p.created = '2012-01-01' > group by u.user_id > order by u.user_id > > If you need something more efficient, summary tables may help - hard to > say without knowing more about the real data. > > Tom > > -- > Sent via pgsql-general mailing list (pgsql-gene...@xxxxxxxxxxxxxx) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general I think I figured it out. I have to add a where clause... select u.user_id, count(b.user_id) as "book_count", coalesce(sum(b.price), 0) as "book_price_total", count(p.user_id) as "pencil_count", coalesce(sum(b.price), 0) as "pencil_price_total" from users u left join books b on b.user_id = u.user_id and b.created = '2012-01-01' left join pencils p on p.user_id = u.user_id and p.created = '2012-01-01' WHERE b.user_id = u.user_id AND p.user_id = u.user_id group by u.user_id order by u.user_id -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
