query problems
От | Andreas Berglund |
---|---|
Тема | query problems |
Дата | |
Msg-id | 4F3C0807.6060200@gmail.com обсуждение исходный текст |
Ответы |
Re: query problems
|
Список | 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
В списке pgsql-general по дате отправления: