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
