a select statement of sub selects ??

[alvin101]

I need a report, a side by side inventory count if you will...

I tried a few statements but just can not get it.

But it would be something like this:

SELECT
	inventory.sku
,    	inventory.description_short
FROM
   	inventory
  JOIN inventory_history
    ON inventory.sku = inventory_history.sku

  (
    SELECT SUM(remaining)
    FROM  inventory_history
WHERE
            inventory_history.store_id = 5
    ) AS qty_cortes,
    (
    SELECT SUM(remaining)

    FROM    inventory_history
WHERE
            inventory_history.store_id = 4

    ) AS qty_rio,
(
    SELECT SUM(remaining)
    FROM  inventory_history
WHERE
            inventory_history.store_id = 5
    ) AS qty_brickbay,
 GROUP BY 
   inventory.sku

Please let me know how I could do this, Thanks, AM

[r937]

why do cortes and brickbay have the same store number?

i decided to give brickbay store number 3

SELECT inventory.sku
     , inventory.description_short
     , SUM(CASE WHEN inventory_history.store_id = 5
                THEN remaining ELSE NULL END) AS qty_cortes
     , SUM(CASE WHEN inventory_history.store_id = 4
                THEN remaining ELSE NULL END) AS qty_rio
     , SUM(CASE WHEN inventory_history.store_id = 3
                THEN remaining ELSE NULL END) AS qty_brickbay
  FROM inventory
INNER
  JOIN inventory_history
    ON inventory_history.sku = inventory.sku
   AND inventory_history.store_id IN ( 5,4,3 )
 GROUP 
    BY inventory.sku