Ask the Expert

An SQL solution for a customer order homework problem

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: