I have a request:

SELECT * 
FROM 
    (SELECT 
         products.*, 
         COUNT(cheque.product_id) AS countOfOrders 
     FROM 
         products
     JOIN 
         products_to_orders AS cheque ON products.id = cheque.product_id
     GROUP BY 
         products.id) AS total
WHERE 
    total.countOfOrders > MAX(countOfOrders);

My task is to find the best selling product in the shop.

In a subquery, I count how many times a product has been ordered.

Next, I need to display from this query only the product that was ordered the most.

I am trying to do it with WHERE. But WHERE disables the use of aggregate functions.

How can I solve my problem?

CodePudding user response：

You can order in the suvquery and take the first.

For mysql

SELECT products.*, count(cheque.product_id) AS countOfOrders FROM products
JOIN products_to_orders AS cheque ON products.id = cheque.product_id
GROUP BY products.id
ORDER by countOfOrders  DESC
LIMIT 1

For sql server

SELECT TOP 1 products.*, count(cheque.product_id) AS countOfOrders FROM products
JOIN products_to_orders AS cheque ON products.id = cheque.product_id
GROUP BY products.id
ORDER by countOfOrders  DESC