Re: Whats the most efficient query for this result?

On 17/01/12 17:51, Nick wrote:
> On Jan 17, 3:33 am, t...@audioboundary.com (Tom Molesworth) wrote:
>> Hi Nick,
>>
>> On 17/01/12 00:18, Nick wrote:
>>
>>
>>> 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...@postgresql.org)
>> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
> Thanks Tom. Thats what I originally thought it would be, but my
> results (disregarding the date clause) show that user 1 has 2 pencils
> instead of 1, and user 3 has 2 books instead of 1.
>
> I guess the LEFT JOIN is joining the first LEFT JOIN instead of the
> users table.
>
> Any other thoughts on how to get books and pencils to individually
> LEFT JOIN the users table?
>
Looking at it again, I think the missing part is the created fields -
you'll probably need both of those in the group by clause as well to get
meaningful numbers. I think that makes:

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(p.price), 0) as "pencil_price_total"
from tst.users u
left join tst.books b on b.user_id = u.user_id and b.created = '2012-01-01'
left join tst.pencils p on p.user_id = u.user_id and p.created =
'2012-01-01'
group by u.user_id, p.created, b.created
order by u.user_id;

Tom