In MYSQL, querying names of products stored in 5 warehouses that has SUM quantity > 100 for each product

user_13894441 0
Viewed 246

please help me to make a query using mysql to show the products(product.name) that have total quantity SUM(goods.quantity) > 100 in all 5 warehouses, specifying the warehouse(warehouse.name) with the greatest quantity of each product. Here is the scheme of all tables for better understanding:

image of warehouses database

I am trying this query:

SELECT product.name AS product, warehouse.name AS warehouse 
FROM product, warehouse, goods 
WHERE goods.productId = product.id AND goods.warehouseId = warehouse.id 
GROUP BY product.name, warehouse.name 
HAVING SUM(goods.quantity)>100;

it gives me the following:

product warehouse
engine New York
right back door New York
main frame London
left back door London
back lights London
steering wheel Sydney
wheel Toronto
car cleaner Toronto

But it only shows products that have goods.quantity > 100, not the SUM(goods.quantity) > 100 in all warehouses.

Besides, it doesn't show warehouse with the greatest quantity of each product, but any warehouse that has this product.

mysql sql
3 Answers

Try adding SUM(goods.quantity) in the selected fields to show that number also you could ORDER BY SUM(goods.quantity) DESC so the highest quantity is at the top.

SELECT product.name AS product, warehouse.name AS warehouse, SUM(goods.quantity) FROM product, warehouse, goods WHERE goods.productId = product.id AND goods.warehouseId = warehouse.id GROUP BY product.name, warehouse.name HAVING SUM(goods.quantity)>100;
user_149317810

You could try using you query as subquery result for count the numer of warehouse

    select products 
    from  (
        SELECT product.name AS product, warehouse.name AS warehouse 
        FROM product
        INNER JOIN warehouse  goods.warehouseId = warehouse.id 
        INNER JOIN  goods goods.productId = product.id AND
        WHERE goods.productId = product.id AND goods.warehouseId = warehouse.id 
        GROUP BY product.name, warehouse.name 
        HAVING SUM(goods.quantity)>100;

    ) t 
    group by products
    having count(distinct warehouse) = 5
user_35223120

You will have to do this step by step. First you need the total quantity and the maximum quantity per product. Thus you can decide whether to show this product and what warehouse to display then. This means an aggregation first, then some joins.

At last you must be prepared that there are ties (two warehouses with the same maximum quantity per product). You can use GROUP_CONCAT to get a list of the top warehouses.

select 
  p.id,
  p.name as product_name,
  group_concat(w.name) as warehouse_names
from
(
  select 
    productid, 
    max(quantity) as max_qantity
  from goods
  group by productid
  having sum(quantity) > 100
) base
join product p on p.id = base.productid
join goods g on g.productid = base.productid and p.quantity = base.max_qantity
join warehouse w on w.id = g.warehouseid
group by p.id
order by product_name;
user_22707620
Related