Re: Whats the most efficient query for this result?

On Jan 17, 2012, at 21:08, Tom Molesworth <tom(at)audioboundary(dot)com> wrote:

> 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:
> 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;
>

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 book records you end up effectively CROSS JOINing them to get 6 sale records which are then aggregated. You have to ensure that 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 separate aggregations for each independent dataset.

David J.