Search code examples
mysqlsqlmysql-error-1054

How to find the product_id with the most occurences and biggest quantity SQL

I am making an application for managing inventory and one of my functions is reporting.

I am trying to make a basic top five product sales report but i simply can't wrap my head at how it should look.

So far i can return the max() of count() for the table e.g

SELECT MAX(product_quantity) FROM 
(SELECT COUNT(quantity) as product_quantity FROM
sales_products) as derived;

Now if i add the product_id in the select i get an error of unknown field:

SELECT product_id, MAX(product_quantity) FROM 
    (SELECT COUNT(quantity) as product_quantity FROM
    sales_products) as derived;

My two questions are why i get the unknown field since i am referencing the table (tried using the table name or the alias derived) AND how to get the top 5 instead of just the first? Thank you very much for your time and patience!

Below are pictures of the erd and the data structure

DB_ERD

DB_structure

Solution

  • If you want a product with the highest number of sales then try the following query

    SELECT product_id, COUNT(quantity) as product_quantity 
FROM sales_products
GROUP BY product_id
HAVING COUNT(quantity) >= ALL(
    SELECT COUNT(quantity)
    FROM sales_products
    GROUP BY product_id 
)

    As mentioned by Damien, you probably need the sum of quantity more than number of records sales_products per product_id. Therefore, in such case the solution should be

    SELECT product_id, SUM(quantity) as product_quantity 
FROM sales_products
GROUP BY product_id
HAVING SUM(quantity) >= ALL(
    SELECT SUM(quantity)
    FROM sales_products
    GROUP BY product_id 
)

    EDIT: (OP question: Related to the amount of results i can get, how can i get top 5 of them?)

    It can be a little tricky since MySQL does not support LIMIT in ALL/IN/ANY subqueries. The workaround can be the following:

    SELECT product_id, SUM(quantity) as product_quantity 
FROM sales_products
GROUP BY product_id
HAVING SUM(quantity) >= (
    SELECT MIN(quantity_sum)
    FROM
    (
        SELECT SUM(quantity) quantity_sum
        FROM sales_products
        GROUP BY product_id 
        ORDER BY SUM(quantity) desc
        LIMIT 5
    ) t
)

    EDIT 2: if you are concerned just about top 5 and you do not care about ties (this means that you can have for example top ten products with the same sum quantity and you can randomly select just 5) then you can use this

    SELECT product_id, SUM(quantity) as product_quantity 
FROM sales_products
GROUP BY product_id
ORDER BY SUM(quantity) desc
LIMIT 5