Q

An SQL solution for a customer order homework problem

How do you select a max value in an SQL customer order table? Learn the answer here.

This Content Component encountered an error
This Content Component encountered an error

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close