SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast alvin101's Avatar
    Join Date
    Sep 2010
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a select statement of sub selects ??

    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:

    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why do cortes and brickbay have the same store number?

    i decided to give brickbay store number 3
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •