How to use Max function on another column from a select

Question

There is query which is asking for favourite products which is bought by each coustomer. i have to select and in the first select i have selected the count of products that each customer bought. in the other select i want to select the maximum of that boughts for each customer.but when i want to select max(previous select column) it gets and error and says it is not defined can any one helps me how to fix this problem. i am very motivated to solve the problem from this way, and i am not willing to use other methods like creating view or something like that. can any one help me on this:

SELECT INN.Maximum,INN.Name, customer.ProductName from
(SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
FROM Orders, [Order Details], Products, Customers
WHERE [Order Details].OrderID = Orders.OrderID
AND [Order Details].ProductID = Products.ProductID
AND Orders.CustomerID = Customers.CustomerID
GROUP BY ContactName, ProductName)customer
INNER JOIN
(SELECT Customers.ContactName AS Name, **MAX(customer.numOftimecustomer)** AS Maximum 
from    Customers, customer 
GROUP BY Customers.ContactName) INN
ON INN.Name = customer.ContactName AND INN.Maximum = customer.NumOftimeCustomer

that part which is mentioned with MAX(customer.numOftimecustomer) ** is the part which gives error and it says the object customer is not defined. is there a way to solve it without view? why is it in this way? since the customer that i defined is not a table?

Answer 1

here is what you want:

select 
    *
 from  (SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
    FROM Orders, [Order Details], Products, Customers
    WHERE [Order Details].OrderID = Orders.OrderID
    AND [Order Details].ProductID = Products.ProductID
    AND Orders.CustomerID = Customers.CustomerID
    GROUP BY ContactName, ProductName)customer
where customer.num = (select max(num) from

(SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
    FROM Orders, [Order Details], Products, Customers
    WHERE [Order Details].OrderID = Orders.OrderID
    AND [Order Details].ProductID = Products.ProductID
    AND Orders.CustomerID = Customers.CustomerID
    GROUP BY ContactName, ProductName)customer2
 where customer2.name = customer.name)