Extract net inventory for each store in each city

Question

The company wants to analyze the scale of inventory kept in stores to estimate the size and design of the fulfillment centers. You need to combine the inventory data, store data, and city data such that your rows represent the net inventory for each store in each city (or inventory in one fulfillment center).

Underline Data: Sakila DB

My Doubt: As the question says "Net Inventory" should I be calculating "Sum" or "Count" of the inventory? my sql query and screenshot of results attached. Please advise if my result is correct?

MySQL query:

SELECT 
    CT.CITY AS "city", ST.STORE_ID AS "store_id",  
    COUNT(INV.INVENTORY_ID) AS 'Net inventory' 
FROM 
    CITY CT
INNER JOIN 
    ADDRESS ADRS ON CT.CITY_ID = ADRS.CITY_ID
INNER JOIN 
    STORE ST ON ADRS.ADDRESS_ID = ST.STORE_ID
INNER JOIN 
    INVENTORY INV ON ST.STORE_ID = INV.STORE_ID
GROUP BY 
    CT.CITY, ST.STORE_ID
ORDER BY 
    'Net inventory';

MySQL result:

Answer 1

Your solution is correct.

If you were to use SUM(INV.INVENTORY_ID) you would be adding all the INVENTORY_ID's together.

The id's exist to guarantee a unique way of identifying each row for relating the tables together, and have nothing to do with inventory counts.

Also, in Sakila, an inventory row represents 1 copy of a film in a store, so COUNT() is appropriate.

In some inventory systems, there might be a column that is actually a summarized count of the # of that product in the store, but Sakila does not have that concept.