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-17 11:33:39 |
Message-ID: | 4F155C93.6060700@audioboundary.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tefft, Michael J | 2012-01-17 12:58:25 | RE: [GENERAL] PostgreSQL counterpart to DBMS_METADATA? |
Previous Message | hubert depesz lubaczewski | 2012-01-17 11:24:36 | Re: PostgreSQL counterpart to DBMS_METADATA? |