|
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.
|
