Need Query for this output

Ankit Shukla

8y857SQL

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kapil Singh Kumawat8y ago
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.
0
Packiaraj Santhiyagu8y ago
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.
+2
Ankit Shukla8y ago
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.
0
Kapil Singh Kumawat8y ago
Can you please the output you are gettingfrom your query
0