query problems

From: Andreas Berglund <email(dot)lists81(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: query problems
Date: 2012-02-15 19:31:19
Message-ID: 4F3C0807.6060200@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi!
I'm trying to query the database of a fictional bookstore to find out
which publisher has sold the most to the bookstore.
This is the database structure

books((book_id), title, author_id, subject_id)
publishers((publisher_id), name, address)
authors((author_id), last_name, first_name)
stock((isbn), cost, retail_price, stock)
shipments((shipment_id), customer_id, isbn, ship_date)
customers((customer_id), last_name, first_name)
editions((isbn), book_id, edition, publisher_id, publication_date)
subjects((subject_id), subject, location)

This is my query

select publisher_id, sum(sum) from ((select publisher_id,
sum(cost*stock) from stock natural join editions group by publisher_id)
UNION (select publisher_id, sum(cost * count) from stock natural join
(select isbn, count(isbn) from shipments group by isbn)a natural join
editions group by publisher_id))a group by publisher_id;

That gets me a table with publisher_id and the total amount of sales for
every publisher. From that I would like to extract the tuple with the
biggest sum. But I can only seem to get the sum itself not the whole
tuple. How do I go about this?

If there's a smarter way to approach the problem then I'm open to
suggestions.

regards
Andreas Berglund

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2012-02-15 19:32:20 Re: postgresql-9.0
Previous Message Tom Lane 2012-02-15 17:54:18 Re: Reassigned Owned Error- unexpected classid 2328