locked
Join 5 Seperate Select Statements RRS feed

  • Question

  • What is the most practical way to join these seperate statements? They would Join on ItemCode!!!

    SELECT

     

    ItemCode, CASE WHEN TransType = 'Imported' THEN SUM(CASE When Quantity IS NULL THEN 0 ELSE Quantity End) ELSE 0 END AS Imported

    FROM

     

    ItemsTrans

    GROUP

     

    By ItemCode, TransType

    SELECT

     

    ItemCode, CASE WHEN TransType = 'Discontinued' THEN SUM(CASE When Quantity IS NULL THEN 0 ELSE Quantity End) ELSE 0 END AS Discontinued

    FROM

     

    ItemsTrans

    GROUP

     

    By ItemCode, TransType

    SELECT

     

    ItemCode, CASE WHEN TransType = 'Received' THEN SUM(CASE When Quantity IS NULL THEN 0 ELSE Quantity End) ELSE 0 END AS Received

    FROM

     

    ItemsTrans

    GROUP

     

    By ItemCode, TransType

    SELECT

     

    ItemCode, CASE WHEN TransType = 'Issued' THEN SUM(CASE When Quantity IS NULL THEN 0 ELSE Quantity End) ELSE 0 END AS Issued

    FROM

     

    ItemsTrans

    GROUP

     

    By ItemCode, TransType

    SELECT

     

    ItemCode, CASE WHEN TransType = 'Ordered' THEN SUM(CASE When Quantity IS NULL THEN 0 ELSE Quantity End) ELSE 0 END AS Ordered

    FROM

     

    ItemsTrans

    GROUP

     

    By ItemCode, TransType

    Monday, May 18, 2009 5:41 PM

Answers

  • You do not need ot join, try this:

    SELECT ItemCode,
    SUM(CASE WHEN TransType = 'Imported' THEN Quantity ELSE 0 END)
    AS Imported,
    SUM(CASE WHEN TransType = 'Discontinued' THEN Quantity ELSE 0
    END) AS Discontinued,
    SUM(CASE WHEN TransType = 'Received' THEN Quantity ELSE 0 END)
    AS Received,
    SUM(CASE WHEN TransType = 'Issued' THEN Quantity ELSE 0 END) AS
    Issued,
    SUM(CASE WHEN TransType = 'Ordered' THEN Quantity ELSE 0 END) AS
    Ordered
    FROM ItemsTrans
    GROUP By ItemCode;

    On SQL Server 2005/2008 you can also use the PIVOT operator.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    • Marked as answer by Codeitup Monday, May 18, 2009 5:54 PM
    Monday, May 18, 2009 5:47 PM
  • SELECT ItemCode, CASE WHEN TransType = 'Imported' THEN SUM(Quantity) ELSE 0 END AS Imported
    , CASE WHEN TransType = 'Discontinued' THEN SUM(Quantity) ELSE 0 END AS Discontinued
    , CASE WHEN TransType = 'Received' THEN SUM(Quantity) ELSE 0 END AS Received
    , CASE WHEN TransType = 'Issued' THEN SUM(Quantity) ELSE 0 END AS Issued
    , CASE WHEN TransType = 'Ordered' THEN SUM(Quantity)ELSE 0 END AS Ordered
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    But I'm not sure that statement does what you want it to.

    Michael Asher
    • Marked as answer by Codeitup Monday, May 18, 2009 5:54 PM
    Monday, May 18, 2009 5:47 PM
  • Is this what you're looking for?:

    SELECT ItemCode
         , SUM(CASE WHEN TransType='Imported' THEN Quantity ELSE 0 END) AS Imported
         , SUM(CASE WHEN TransType='Discontinued' THEN Quantity ELSE 0 END) AS Discontinued
         , SUM(CASE WHEN TransType='Received' THEN Quantity ELSE 0 END) AS Received
         , SUM(CASE WHEN TransType='Issued' THEN Quantity ELSE 0 END) AS Issued
         , SUM(CASE WHEN TransType='Ordered' THEN Quantity ELSE 0 END) AS Ordered
    FROM         ItemsTrans
    GROUP By ItemCode
    

    --Brad
    • Marked as answer by Codeitup Monday, May 18, 2009 5:55 PM
    Monday, May 18, 2009 5:49 PM
  • I'm not sure, but maybe you want something like this:

    SELECT
       ItemCode,
       SUM(CASE WHEN TransType = 'Imported' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Imported,
       SUM(CASE WHEN TransType = 'Discontinued' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Discontinued,
       SUM(CASE WHEN TransType = 'Received' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Received,
       SUM(CASE WHEN TransType = 'Issued' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Issued,
       SUM(CASE WHEN TransType = 'Ordered' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Ordered
    FROM
       ItemsTrans
    GROUP BY
       ItemCode, TransType
    • Marked as answer by Codeitup Monday, May 18, 2009 5:55 PM
    Monday, May 18, 2009 5:51 PM

All replies

  • Wow that looks hideous!!!

    SELECT ItemCode, CASE WHEN TransType = 'Imported' THEN SUM(Quantity) ELSE 0 END AS Imported
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    SELECT ItemCode, CASE WHEN TransType = 'Discontinued' THEN SUM(Quantity) ELSE 0 END AS Discontinued
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    SELECT ItemCode, CASE WHEN TransType = 'Received' THEN SUM(Quantity) ELSE 0 END AS Received
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    SELECT ItemCode, CASE WHEN TransType = 'Issued' THEN SUM(Quantity) ELSE 0 END AS Issued
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    SELECT ItemCode, CASE WHEN TransType = 'Ordered' THEN SUM(Quantity)ELSE 0 END AS Ordered
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    Monday, May 18, 2009 5:44 PM
  • You do not need ot join, try this:

    SELECT ItemCode,
    SUM(CASE WHEN TransType = 'Imported' THEN Quantity ELSE 0 END)
    AS Imported,
    SUM(CASE WHEN TransType = 'Discontinued' THEN Quantity ELSE 0
    END) AS Discontinued,
    SUM(CASE WHEN TransType = 'Received' THEN Quantity ELSE 0 END)
    AS Received,
    SUM(CASE WHEN TransType = 'Issued' THEN Quantity ELSE 0 END) AS
    Issued,
    SUM(CASE WHEN TransType = 'Ordered' THEN Quantity ELSE 0 END) AS
    Ordered
    FROM ItemsTrans
    GROUP By ItemCode;

    On SQL Server 2005/2008 you can also use the PIVOT operator.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    • Marked as answer by Codeitup Monday, May 18, 2009 5:54 PM
    Monday, May 18, 2009 5:47 PM
  • SELECT ItemCode, CASE WHEN TransType = 'Imported' THEN SUM(Quantity) ELSE 0 END AS Imported
    , CASE WHEN TransType = 'Discontinued' THEN SUM(Quantity) ELSE 0 END AS Discontinued
    , CASE WHEN TransType = 'Received' THEN SUM(Quantity) ELSE 0 END AS Received
    , CASE WHEN TransType = 'Issued' THEN SUM(Quantity) ELSE 0 END AS Issued
    , CASE WHEN TransType = 'Ordered' THEN SUM(Quantity)ELSE 0 END AS Ordered
    FROM         ItemsTrans
    GROUP By ItemCode, TransType

    But I'm not sure that statement does what you want it to.

    Michael Asher
    • Marked as answer by Codeitup Monday, May 18, 2009 5:54 PM
    Monday, May 18, 2009 5:47 PM
  • Is this what you're looking for?:

    SELECT ItemCode
         , SUM(CASE WHEN TransType='Imported' THEN Quantity ELSE 0 END) AS Imported
         , SUM(CASE WHEN TransType='Discontinued' THEN Quantity ELSE 0 END) AS Discontinued
         , SUM(CASE WHEN TransType='Received' THEN Quantity ELSE 0 END) AS Received
         , SUM(CASE WHEN TransType='Issued' THEN Quantity ELSE 0 END) AS Issued
         , SUM(CASE WHEN TransType='Ordered' THEN Quantity ELSE 0 END) AS Ordered
    FROM         ItemsTrans
    GROUP By ItemCode
    

    --Brad
    • Marked as answer by Codeitup Monday, May 18, 2009 5:55 PM
    Monday, May 18, 2009 5:49 PM
  • I'm not sure, but maybe you want something like this:

    SELECT
       ItemCode,
       SUM(CASE WHEN TransType = 'Imported' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Imported,
       SUM(CASE WHEN TransType = 'Discontinued' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Discontinued,
       SUM(CASE WHEN TransType = 'Received' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Received,
       SUM(CASE WHEN TransType = 'Issued' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Issued,
       SUM(CASE WHEN TransType = 'Ordered' THEN ISNULL(Quantity, 0) ELSE 0 END) AS Ordered
    FROM
       ItemsTrans
    GROUP BY
       ItemCode, TransType
    • Marked as answer by Codeitup Monday, May 18, 2009 5:55 PM
    Monday, May 18, 2009 5:51 PM
  • I thought I tried that, but I was getting multiple rows with the same itemcode! Thanks all...
    Monday, May 18, 2009 5:55 PM
  • you were obtaining multiple rows with same ItemCode, because you were grouping by both ItemCode and TransType, then you received the same itemcode for each transtype...

    if you want only one row with an itemcode then you need only:

    GROUP BY ItemCode
    Monday, May 18, 2009 6:02 PM