Re: query problems

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andreas Berglund
Sent: Wednesday, February 15, 2012 2:31 PM
To: pgsql-general(at)postgresql(dot)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.