On Jan 17, 2012, at 21:08, Tom Molesworth <tom@audioboundary.com> wrote:
> On 17/01/12 17:51, Nick wrote:
>> On Jan 17, 3:33 am, t...@audioboundary.com (Tom Molesworth) wrote:
> Looking at it again, I think the missing part is the created fields - you'll probably need both of those in the group
byclause 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;
>
Why?
What reason is there to include the "created" fields in the GROUP BY but not place them into the corresponding SELECT
output?
The true issue is that the aggregates are operating on two independent joins. If you have 3 pencil records and two
bookrecords you end up effectively CROSS JOINing them to get 6 sale records which are then aggregated. You have to
ensurethat at most one record is on the right side of each join so that 1 X 1 -> 1. You can only do this by performing
separateaggregations for each independent dataset.
David J.