Whats the most efficient query for this result?

From: Nick <nboutelier(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Whats the most efficient query for this result?
Date: 2012-01-17 00:18:48
Message-ID: 7ebf0924-621d-4c27-a987-bb73df71f4be@l17g2000yqb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Tefft 2012-01-17 00:27:11 PostgreSQL counterpart to DBMS_METADATA?
Previous Message Craig Ringer 2012-01-16 23:43:44 Re: postgres Windows distribution - openssl support