Search for the most popular product in the shop

June 19, 2022

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?

>Solution :

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