MySQL sum of subquery containing join

I have a table containing inventories in the form

ID |Product ID | Movement |  Cumulative Quantity | Store ID
=================================================|=========
1  | 1         |  100     |  100                 |  1 
2  | 1         |  -4      |  96                  |  1
3  | 1         |  -1      |  95                  |  1
4  | 2         |  100     |  100                 |  1
5  | 1         |  100     |  100                 |  2

Every line basically has a movement of stock and the last line MAX(ID) contains cumulative_quantity which contains the stock available

Another table containing products

ID | Product Name
====================
1  | Apple Juice
2  | Orange Juice

I'm looking for output in the form

Product ID | Product Name | Total Quantity at all stores
========================================================
1          | Apple Juice  | 195
2          | Orange Juice | 100

This is a simplified version of the following table

Store ID   | Product ID | Product Name | Cumulative Quantity
========================================================
1         开发者_C百科 | 1          | Apple Juice  | 95
1          | 2          | Orange Juice | 100
2          | 1          | Apple Juice  | 100
2          | 2          | Orange Juice | 0

This is a relatively simple SUM() aggregate with a JOIN. SUM(Movement) will result in the total quantity available across all stores.

SELECT
  inventories.productId,
  productName,
  SUM(Movement) AS `Total Quantity`
FROM inventories JOIN  products ON inventories.productId = products.productId
GROUP BY inventories.productId, productName

There's several ways to do this here's one

Sub-query in From Clause using MAX

SELECT products.id as `Product ID`,  
       products.`Product Name`, 
       SUM(inventory.`Cumulative Quantity`) as Total Quantity at all stores
FROM products
     INNER JOIN inventory
     ON products.`id` = inventory.`id`
     INNER JOIN 
     (
     SELECT ( max(`id`)  as `id`,
              `product id`,
              `store id`
     FROM 
           `inventory`
     GROUP BY
              `product id`,
              `store id`) maxinventory
     ON inventory.`id` = maxinventory.`id`
GROUP BY
      products.`id`,  
       products.`Product Name`

Here's another using IN

SELECT products.id as `Product ID`,  
       products.`Product Name`, 
       SUM(inventory.`Cumulative Quantity`) as Total Quantity at all stores
FROM products
     INNER JOIN inventory
     ON products.`id` = inventory.`id`
WHERE
      inventory.`id ` IN
     (
     SELECT ( max(`id`)  as `id`,
     FROM 
           `inventory`
     GROUP BY
              `product id`,
              `store id`) 
GROUP BY
      products.`id`,  
       products.`Product Name`

The trick is to use a subquery to find the current inventory records, i.e. those that have the maximum id for each product_id, store_id pair:

SELECT
  product_id, name, SUM( cumulative_qty ) AS total_qty
FROM
  (SELECT MAX(id) AS id FROM inventories GROUP BY product_id, store_id) AS cur
  NATURAL JOIN inventories
  JOIN products ON products.id = inventories.product_id
GROUP BY product_id

(BTW, I suspect that you want an index on inventories (product_id, store_id, id) to make this query reasonably efficient. And of course I assume that the id columns are primary keys.)