Thread: query problems

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andreas Berglund
Sent: Wednesday, February 15, 2012 2:31 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] query problems


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

-----------------------------------------------------------

Need clarification:

Is this:

1) a bookstore that doesn't exist
2) a bookstore that only sells fiction
3) All the above

Also, what happens if two publishers sold the same maximum amount?

You should really get into the habit of giving your calculated fields names
so that you can avoid stuff like " SUM(SUM) ".

It may be your e-mail client but you should also try to present you
information with generous use of whitespace and, if possible, indenting; I
am not inclined to copy-paste and perform the indenting in order to figure
out what seems to be a basic textbook/homework problem.

Once you know what the maximum amount is (from a sub-query) you then link
that back onto the original table if the form of a "WHERE" clause or,
possibly, a JOIN.

SELECT * FROM publishers WHERE publisher_id IN ( "your big long query goes
here; with an ORDER BY DESC and LIMIT 1; without the SUM(SUM) in the select
list" );

There likely is a more succinct way to write this but I'd need to spend more
time than I have right now.

David J.