From: | Tom Molesworth <tom(at)audioboundary(dot)com> |
---|---|
To: | Nick <nboutelier(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Whats the most efficient query for this result? |
Date: | 2012-01-18 02:08:57 |
Message-ID: | 4F1629B9.4060007@audioboundary.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 17/01/12 17:51, Nick wrote:
> On Jan 17, 3:33 am, t(dot)(dot)(dot)(at)audioboundary(dot)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(dot)(dot)(dot)(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2012-01-18 02:12:26 | Re: PG synchronous replication and unresponsive slave |
Previous Message | Manoj Govindassamy | 2012-01-18 01:54:04 | Re: PG synchronous replication and unresponsive slave |