Post reply

How to Calculate Difference between 2 queries Columns in union ALL

  • July 16, 2017 at 2:40 pm

    #324531

    Hello Good Afternoon,

    Can you please help me with below query how to get the another column in the output as (First_Bal - S_Balance) ?


    SELECT
    COL1, Col2, Col3, First_Bal, First_Qty, S_Balance
    --- New Column which is First_bal - S_Balance here how???
    FROM (
        SELECT COL1, Col2, Col3, SUM(Balance) First_Bal,
        Sum(Quantity) First_Qty, 0 as S_Balance
        from Table2
        Group by COL1, Col2, Col3

        UNION ALL,
        
        SELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty,
        SUM(MyBalance) as S_Balance
        from Table1
        Group by COL1, Col2, Col3
      )

    Thank you in advance
    ASita

  • July 16, 2017 at 9:16 pm

    #1951080

    Hello there can somebody please give suggestions.

    Thank you

  • July 16, 2017 at 11:26 pm

    #1951083

    asita - Sunday, July 16, 2017 2:40 PM

    Hello Good Afternoon,

    Can you please help me with below query how to get the another column in the output as (First_Bal - S_Balance) ?


    SELECT
    COL1, Col2, Col3, First_Bal, First_Qty, S_Balance
    --- New Column which is First_bal - S_Balance here how???
    FROM (
        SELECT COL1, Col2, Col3, SUM(Balance) First_Bal,
        Sum(Quantity) First_Qty, 0 as S_Balance
        from Table2
        Group by COL1, Col2, Col3

        UNION ALL,
        
        SELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty,
        SUM(MyBalance) as S_Balance
        from Table1
        Group by COL1, Col2, Col3
      )

    Thank you in advance
    ASita


    SELECT 
    COL1, Col2, Col3, First_Bal, First_Qty, S_Balance
    , First_bal - S_Balance AS New_Balance
    FROM (
        SELECT COL1, Col2, Col3, SUM(Balance) First_Bal, 
        Sum(Quantity) First_Qty, 0 as S_Balance
        from Table2
        Group by COL1, Col2, Col3

        UNION ALL,
        
        SELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty, 
        SUM(MyBalance) as S_Balance
        from Table1
        Group by COL1, Col2, Col3
      )

    How to post data/code on a forum to get the best help.

  • July 17, 2017 at 1:07 am

    #1951085

    adding ALIAS NAME for a derived table.

    SELECT
    COL1, Col2, Col3, First_Bal, First_Qty, S_Balance
    , First_bal - S_Balance AS New_Balance
    FROM (
        SELECT COL1, Col2, Col3, SUM(Balance) First_Bal,
        Sum(Quantity) First_Qty, 0 as S_Balance
        from Table2
        Group by COL1, Col2, Col3

        UNION ALL,
       
        SELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty,
        SUM(MyBalance) as S_Balance
        from Table1
        Group by COL1, Col2, Col3
      ) tem

  • July 17, 2017 at 5:15 am

    #1951113

    Anjan@Sql - Monday, July 17, 2017 1:07 AM

    adding ALIAS NAME for a derived table.

    SELECT
    COL1, Col2, Col3, First_Bal, First_Qty, S_Balance
    , First_bal - S_Balance AS New_Balance
    FROM (
        SELECT COL1, Col2, Col3, SUM(Balance) First_Bal,
        Sum(Quantity) First_Qty, 0 as S_Balance
        from Table2
        Group by COL1, Col2, Col3

        UNION ALL,
       
        SELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty,
        SUM(MyBalance) as S_Balance
        from Table1
        Group by COL1, Col2, Col3
      ) tem

    You might want to remove the comma after UNION ALL too.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 17, 2017 at 5:44 am

    #1951117

    Yes that's correct Chris..

    SELECT
    COL1, Col2, Col3, First_Bal, First_Qty, S_Balance
    , First_bal - S_Balance AS New_Balance
    FROM (
    SELECT COL1, Col2, Col3, SUM(Balance) First_Bal,
    Sum(Quantity) First_Qty, 0 as S_Balance
    from Table2
    Group by COL1, Col2, Col3

    UNION ALL
    SELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty,
    SUM(MyBalance) as S_Balance
    from Table1
    Group by COL1, Col2, Col3
    ) tem

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply