Warehouse Stock Value Including Empty Sites — SQL Problem
Finance wants the value of stock held at each warehouse, at cost: the sum over its inventory rows of qty × unit_cost.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #380
Problem statement
Finance wants the value of stock held at each warehouse, at cost: the sum over its inventory rows of qty × unit_cost. A newly opened warehouse with no inventory rows must still be listed, with a value of 0.
Return warehouse_id, city and stock_value (rounded to 2 decimal places). Order the rows by stock_value highest first, then by warehouse_id.
Tables
Table: Warehouse
| Column | Type |
|---|---|
| warehouse_id | int |
| city | varchar |
Primary key: warehouse_id.
One row per warehouse of the chain.
Table: Inventory
| Column | Type |
|---|---|
| warehouse_id | int |
| sku | varchar |
| qty | int |
Primary key: warehouse_id, sku.
Units of a SKU on hand at a warehouse.
Table: ProductCost
| Column | Type |
|---|---|
| sku | varchar |
| unit_cost | decimal |
Primary key: sku.
The landed cost per unit in rupees. Every SKU in Inventory has a row here.
Examples
Example 1
Warehouse
| warehouse_id | city |
|---|---|
| 1 | Bengaluru |
| 2 | Pune |
| 3 | Kolkata |
| 4 | Jaipur |
Inventory
| warehouse_id | sku | qty |
|---|---|---|
| 1 | GRO-1001 | 50 |
| 1 | DAI-2002 | 120 |
| 2 | GRO-1001 | 20 |
| 2 | SNK-6001 | 400 |
| 3 | HOM-5001 | 10 |
ProductCost
| sku | unit_cost |
|---|---|
| GRO-1001 | 512.5 |
| DAI-2002 | 68.25 |
| SNK-6001 | 21.4 |
| HOM-5001 | 72 |
Output
| warehouse_id | city | stock_value |
|---|---|---|
| 1 | Bengaluru | 33815 |
| 2 | Pune | 18810 |
| 3 | Kolkata | 720 |
| 4 | Jaipur | 0 |
How to solve Warehouse Stock Value Including Empty Sites
The answer has one row per warehouse, so the query starts from Warehouse and LEFT JOINs the inventory; a warehouse with no stock then appears once with NULLs on the inventory side. The cost must also be joined with a LEFT JOIN (or inside a subquery) — an inner join to ProductCost after the outer join would throw away the empty warehouse again, because its NULL sku matches no cost row.
Grouping by warehouse, SUM(i.qty * c.unit_cost) is the value at cost. For the empty warehouse every product in the sum is NULL, so the sum is NULL; COALESCE(…, 0) reports it as 0 as the statement asks. Rounding to two places keeps the money value identical across engines.
The alternatives compute the value per warehouse first — in a correlated scalar subquery or in a grouped derived table joined back with a LEFT JOIN — and default missing values to 0. All three read each inventory row once.
Reference solution (MySQL)
SELECT w.warehouse_id, w.city,
ROUND(COALESCE(SUM(i.qty * c.unit_cost), 0), 2) AS stock_value
FROM Warehouse w
LEFT JOIN Inventory i ON i.warehouse_id = w.warehouse_id
LEFT JOIN ProductCost c ON c.sku = i.sku
GROUP BY w.warehouse_id, w.city
ORDER BY stock_value DESC, w.warehouse_idAnother way
SELECT w.warehouse_id, w.city, ROUND(COALESCE((SELECT SUM(i.qty * c.unit_cost) FROM Inventory i JOIN ProductCost c ON c.sku = i.sku WHERE i.warehouse_id = w.warehouse_id), 0), 2) AS stock_value FROM Warehouse w ORDER BY stock_value DESC, w.warehouse_idAnother way
SELECT w.warehouse_id, w.city, ROUND(IFNULL(v.val, 0), 2) AS stock_value FROM Warehouse w LEFT JOIN (SELECT i.warehouse_id, SUM(i.qty * c.unit_cost) AS val FROM Inventory i JOIN ProductCost c ON c.sku = i.sku GROUP BY i.warehouse_id) v ON v.warehouse_id = w.warehouse_id ORDER BY stock_value DESC, w.warehouse_id← March POS Bills by Day of the Week · Supplier Average Lead Time in Days →