How to display the warehouse with the largest number of goods and a unique product?

Question

SQL

The essence of the task. Display those products (product.name), the balance of which in all warehouses in total exceeds 100 units (goods.quantity), indicating the warehouse (warehouse.namme) where the largest quantity is located.

select distinct product.name, warehouse.name, Sum(goods.quantity) AS "Count"
from product, warehouse, goods
where (goods.productId = product.id and goods.warehouseId = warehouse.id and goods.quantity >100  )
group by warehouse.name, product.name
order by warehouse.name asc, product.name asc

I have a problem with how to remove unnecessary warehouses, take a warehouse where the largest number of goods.

Answer 1

select distinct p.name, w.name, sum(g.quantity) as "count"
from product p, warehouse w, goods g
where (goods.productId = product.id and goods.warehouseId = warehouse.id
and p.id in
(select productid from
(select productid, sum(quantity) from goods group by productid having sum(quantity) > 100) sq1
) sq2
group by p.name, w.name
order by sum(g.quantity) desc
Using two subqueries .Crooked, but should work.