Home Forums SQL Server 2008 T-SQL (SS2K8) How to Calculate Difference between 2 queries Columns in union ALL RE: How to Calculate Difference between 2 queries Columns in union ALL

  • 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