Warehouse Stock Value Including Empty Sites

Easy
380.

Warehouse Stock Value Including Empty Sites

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

ColumnType
warehouse_idint
cityvarchar

Primary key: warehouse_id.

One row per warehouse of the chain.

Table: Inventory

ColumnType
warehouse_idint
skuvarchar
qtyint

Primary key (together): warehouse_id, sku.

Units of a SKU on hand at a warehouse.

Table: ProductCost

ColumnType
skuvarchar
unit_costdecimal

Primary key: sku.

The landed cost per unit in rupees. Every SKU in Inventory has a row here.

Example

The rows must come back in the order shown.

Warehouse

warehouse_idcity
1Bengaluru
2Pune
3Kolkata
4Jaipur

Inventory

warehouse_idskuqty
1GRO-100150
1DAI-2002120
2GRO-100120
2SNK-6001400
3HOM-500110

ProductCost

skuunit_cost
GRO-1001512.5
DAI-200268.25
SNK-600121.4
HOM-500172

Output

warehouse_idcitystock_value
1Bengaluru33815
2Pune18810
3Kolkata720
4Jaipur0
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
warehouse_idint
cityvarchar

Primary key: warehouse_id.

One row per warehouse of the chain.

Table: Inventory

ColumnType
warehouse_idint
skuvarchar
qtyint

Primary key: warehouse_id, sku.

Units of a SKU on hand at a warehouse.

Table: ProductCost

ColumnType
skuvarchar
unit_costdecimal

Primary key: sku.

The landed cost per unit in rupees. Every SKU in Inventory has a row here.

Examples

Example 1

Warehouse

warehouse_idcity
1Bengaluru
2Pune
3Kolkata
4Jaipur

Inventory

warehouse_idskuqty
1GRO-100150
1DAI-2002120
2GRO-100120
2SNK-6001400
3HOM-500110

ProductCost

skuunit_cost
GRO-1001512.5
DAI-200268.25
SNK-600121.4
HOM-500172

Output

warehouse_idcitystock_value
1Bengaluru33815
2Pune18810
3Kolkata720
4Jaipur0

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_id

Another 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_id

Another 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 →