Search Postgresql Archives

Re: Whats the most efficient query for this result?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Jan 17, 3:33 am, t...@xxxxxxxxxxxxxxxxx (Tom Molesworth) wrote:
> Hi Nick,
>
> On 17/01/12 00:18, Nick wrote:
>
>
>
>
>
>
>
>
>
> > I have three tables (users, books, pencils) and would like to get a
> > list of all users with a count and total price of their books and
> > pencils for 2012-01-01...
>
> > So with this data...
>
> > users (user_id)
> > 1
> > 2
> > 3
>
> > books (user_id, price, created)
> > 1 | $10 | 2012-01-01
> > 1 | $10 | 2012-01-01
> > 3 | $10 | 2012-01-01
>
> > pencils
> > 1 | $.50 | 2012-01-02
> > 3 | $.50 | 2012-01-01
> > 3 | $.50 | 2012-01-02
>
> > 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

I think I figured it out. I have to add a where clause...

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'
WHERE b.user_id = u.user_id AND p.user_id = u.user_id
group by u.user_id
order by u.user_id

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux