MySQL Query - Sum the Capacity of Multiple Warehouses

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.

Answer1:

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

Answer2:

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

Answer3:

You should look up the mysql manual for these words:

    <li>inner join</li> <li>group by</li> <li>sum()</li> </ul>

    These can help you assembly the query you want.

    人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:MySQL Query - Sum the Capacity of Multiple Warehouses