## How to Calculate Difference between 2 queries Columns in union ALL

 Author Message asita Say Hey Kid Group: General Forum Members Points: 662 Visits: 337 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 advanceASita asita Say Hey Kid Group: General Forum Members Points: 662 Visits: 337 Hello there can somebody please give suggestions. Thank you DesNorton SSCertifiable Group: General Forum Members Points: 5094 Visits: 3963 +xasita - Sunday, July 16, 2017 2:40 PMHello 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 advanceASita`SELECT COL1, Col2, Col3, First_Bal, First_Qty, S_Balance, First_bal - S_Balance AS New_BalanceFROM ( 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.Make sure that you include code in the appropriate IFCode tags. Anjan@Sql Ten Centuries Group: General Forum Members Points: 1250 Visits: 1498 adding ALIAS NAME for a derived table.SELECT COL1, Col2, Col3, First_Bal, First_Qty, S_Balance, First_bal - S_Balance AS New_BalanceFROM ( 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 ChrisM@Work SSC-Forever Group: General Forum Members Points: 41941 Visits: 20008 +xAnjan@Sql - Monday, July 17, 2017 1:07 AMadding ALIAS NAME for a derived table.SELECT COL1, Col2, Col3, First_Bal, First_Qty, S_Balance, First_bal - S_Balance AS New_BalanceFROM ( 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 ) temYou 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps Anjan@Sql Ten Centuries Group: General Forum Members Points: 1250 Visits: 1498 Yes that's correct Chris..SELECT COL1, Col2, Col3, First_Bal, First_Qty, S_Balance, First_bal - S_Balance AS New_BalanceFROM ( SELECT COL1, Col2, Col3, SUM(Balance) First_Bal, Sum(Quantity) First_Qty, 0 as S_Balance from Table2 Group by COL1, Col2, Col3 UNION ALLSELECT COL1, Col2, Col3, 0 First_Bal, 0 First_Qty, SUM(MyBalance) as S_Balance from Table1 Group by COL1, Col2, Col3 ) tem