Need Query for this output

Question

Hello, I have 2 tables. Table1 Customer | Amount --------------|------------ A | 10 A | 15 B | 10 B | 5 A | 20 Table2 Customer | Amount --------------|------------ A | 5 A | 10 B | 8 B | 5 I need to get (total amount of Table1) - (total amount of Table2) group by Customer Output Customer | Amount --------------|------------ A | 30 (45 - 15) B | 2 (15 - 13) My Query: Select Table1.Customer, sum(Table1.Amount) - sum(Table2.Amount) from Table1 inner join Table2 On Table1.Customer = Table2.Customer group by Table1.Customer But it gives wrong output. Please help. Thanks

Packiaraj Santhiyagu · Answer

Hi, Try this below code, select * into #temp1 from ( select a.customer, sum (a.amount)amt from table1 a group by a.customer)a select * into #temp2 from ( select b.customer, sum (b.amount)amt from table2 b group by b.customer)b select a.customer,a.amt-b.amt from #temp1 a inner join #temp2 b on a.customer=b.customer I worked out past it. it will work. You can not write this as sigle query so, use stored procedure. Accept as answer, if it helps.

Kapil Singh Kumawat · Answer

Hello Ankit, Try below code it will give the required output that you are looking - Create table #temp1 ( customer varchar (10), amount INT ) Create table #temp2 ( customer varchar (10), amount INT ) INSERT INTO #temp1 values ( 'A' ,10),( 'A' ,15),( 'B' ,10),( 'B' ,5),( 'A' ,20) INSERT INTO #temp2 values ( 'A' ,5),( 'A' ,10),( 'B' ,8),( 'B' ,5) SELECT * FROM #temp1 SELECT * FROM #temp2 ; WITH CTE AS ( SELECT SUM (amount) total, customer FROM #temp1 GROUP BY customer ), CTE2 AS ( SELECT SUM (amount)total, customer FROM #temp2 GROUP BY customer ) SELECT C1.total - C2.total as total, c1.customer FROM CTE C1 INNER JOIN CTE2 C2 ON C1.customer = C2.customer Let me know if it helps you.

Ankit Shukla · Answer

I am getting: A 45 B 4 And I can understand this. Sum of CustomerA of table1 is 45. Then 45 * 2 (There are 2 customers with Name A in table2) - Sum of CustomerA of table2 is 15. Then 15 * 3 (There are 3 customers with Name A in table1). So it comes (45*2) - (15*3) = 45 for A. Same as for B.

Kapil Singh Kumawat · Answer

Can you please the output you are gettingfrom your query