On 17/01/12 17:51, Nick wrote:
On Jan 17, 3:33 am, t...@xxxxxxxxxxxxxxxxx (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...@xxxxxxxxxxxxxx)
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
