MySQL Query - Sum the Capacity of Multiple Warehouses

Question

We have a MySQL database that has two tables, Warehouse and Crate

Warehouse:
WarehouseID (primary key)
Location (varchar)

Crate:
CrateID (primary key)
Warehouse (foreign key to a Warehouse record)
Max_Capacity (int) (the amount of boxes that can be packed into a crate)

I would like to write a query that returns a sum of all the crates' max_capacity for each Warehouse. I'm having trouble with duplicate entries and inner joins, so what I'm hoping for is something like:

LOCATION:    WAREHOUSE_ID:  TOTAL_CAPACITY:
Shoreline    1              60
Bellevue     2              120
Ballard      3              200

Each warehouse might have 10 or so crates.

score 1 · Answer 1 · answered Nov 12 '11 at 16:20

1

select w.location, w.warehouse_id, sum(c.capacity) as total_capacity 
from warehouse w, crate c 
where w.warehouse_id = c.warehouse_id 
group by c.warehouse_id

answered Nov 12 '11 at 16:20

ComfortablyNumb

3,391
2
14
15

score 0 · Answer 2 · answered Nov 12 '11 at 16:15

0

You should look up the mysql manual for these words:

inner join
group by
sum()

These can help you assembly the query you want.

answered Nov 12 '11 at 16:15

deejayy

760
3
9

score 0 · Accepted Answer · answered Nov 12 '11 at 16:16

just group by the warehouses:

(assuming the foreign key in the crate table is called warehouseID)

SELECT 
  warehouse.location, 
  warehouse.warehouseID, 
  SUM(crate.max_capacity) AS total_capacity
FROM warehouse
INNER JOIN crate
  ON crate.warehouseID = warehouse.warehouseID
GROUP BY
  warehouse.warehouseID

MySQL Query - Sum the Capacity of Multiple Warehouses

3 Answers3

Linked