How do I select either the max value after I have summed the quantity or the top row?
Select Customer. *, SUM(Quantity) as Total from Customer, Orders o1 where customer.cust_ID = o1.cust_ID and quantity IN (Select Quantity from orders o2 where o1.cust_ID = o2.cust_ID) group by customer.cust_ID, cust_name, Region, Phone order by Total desc;
The row num = 1 function is not working for me.
This is a classic homework assignment question: "Find the customer who ordered the most, along with the total order quantity."
Your attempted solution has a few problems. Your GROUP BY will not work unless the customer table contains exactly those columns and no others. This is a direct consequence of using the dreaded, evil "select star," which means all columns. Since with this notation none of those columns is aggregated, they must all appear in the GROUP BY clause or else you'll get a syntax error. (Unless you're using MySQL. In MySQL it would work, and work correctly, too. See Debunking GROUP BY myths for a detailed explanation; note this article is somewhat advanced.) One way of avoiding this issue is to "push down" the aggregation into a subquery, so that there is no GROUP BY clause in the outer query, and you can use "select star" if you insist.
Your "quantity IN (correlated subquery)" condition doesn't make sense. In effect, it says you want all orders which have a quantity that is equal to the quantity in any of the orders made by the same customer as this customer. You should just leave that out, because every customer order quantity is an order quantity of that customer. What you're really after, of course, is the total quantity of the customer's orders, and then you'll pick the greatest of all those total quantities, and the customer it belongs to.
Finally, your "row number = 1" idea will work, but it uses different syntax depending on each different database system. It's either TOP, or LIMIT, or FETCH FIRST or something like that. (Look it up in your SQL reference manual if you want to go that route.)
Is there any other solution? Why, yes, there is.
SELECT Customer.cust_ID , Customer.cust_name , Customer.Region , Customer.Phone , o1.Total FROM Customer INNER JOIN ( SELECT cust_ID , SUM(quantity) AS Total FROM Orders GROUP BY cust_ID ) AS o1 ON o1.cust_ID = Customer.cust_ID WHERE o1.Total = ( SELECT MAX(Total) FROM ( SELECT cust_ID , SUM(quantity) AS Total FROM Orders GROUP BY cust_ID ) AS o2 ) )
I'm not going to tell you that this works. If you are tempted to hand it in as your homework assignment, you will have to decide for yourself whether it is correct. The best way would be to test it.
This was first published in January 2009