Maximum Quantity Problem

[jimmy89 0]

Hi,I am just starting to play around with Postgres SQL and have stumbled apon a problem.I have a table that has quantity's from 'orders'. I can run the following query;

SELECT prodID, description, SUM(quantity) FROM orderItem NATURAL JOIN product GROUP BY prodID, description ORDER BY SUM(quantity) DESC LIMIT 1 ;

which gives the the 'top selling' product and displays only the top selling product. What I would like to work out is if there is two 'top selling' products, how can i display only the two products (or more products if they are all equal first)Thanks

[k_nitin_r 8]

Hi!You could modify the query so that you do not limit the results to only one query result by taking off the "LIMIT 1". Then, you need to add in a WHERE clause that checks if the sum is equal to the maximum quantity within a subquery.Practically, you would want to display a TOP n list on your application's interface for which you would use your query as-is and increase the limit to, for example, ten query results. If the tenth and the eleventh products have the same quantity sales, then so be it - adding in the logic to check if the eleventh or any subsequent results match the quantity of the tenth isn't really worth working out.

[vhortex 1]

Without doing any subqueries, you can use my quick tips with dealing with high scores, popular products and such using "SUM" and "group by". Check how it can be done using this link http://forums.xisto.com/no_longer_exists/

The article was written using MySQL as a base database but you can apply the same thing with PostGre, Oracle and even MSSQL.

*************

To answer the question, you need to issue an extra query..

SELECT prodID, description, SUM(quantity) FROM orderItem NATURAL JOIN product  WHERE SUM(quantity) = topcount GROUP BY prodID, description ORDER BY SUM(quantity) DESC LIMIT 1;

What to do:

1. issue the original query then store SUM(quantity) into a variable called topcount.
2. issue the same query but add a where clause checking for all values that have SUM equal to topcount then use this result set.
Edited October 14, 2010 by vhortex (see edit history)