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