15,991,544 members
1.00/5 (1 vote)
See more:
Dear sir
I have two sql table Table1 and Table2 which have the following data:

Table1
```ID	 ItemName	Quantity
1	 Collar		100
2	 freeze		200
3	 collar		400
4	 freeze		200
5	 tv		200
6	 tv		500```

Table2
```ID	 ItemName	Quantity
1	 Collar		70
2	 collar		60
3	 tv		200
4	 freeze		50
5	 tv		90
6	 freeze		300```

I want to find the difference of Sum of Quantity of both tables. And want to calculate like:

Result
```ItemName	Quantity
Collar		370
freeze		50
tv		410```

Posted
Updated 15-Sep-14 22:21pm
v5
Kornfeld Eliyahu Peter 16-Sep-14 3:59am
Have you done anything so far? Please show some effort (search or code)! As is it ain't a question...
George Jonsson 16-Sep-14 3:59am
Is this an SQL or C# question?
Sinisa Hajnal 16-Sep-14 4:07am
This is SQL question, not C#. google sql table join, do some research and then ask again when / if you have some concrete problem.
osmo33 16-Sep-14 4:21am
Gihan Liyanage 16-Sep-14 6:24am
See my new solution. I have checked it again. and its working fine.

## Solution 4

Here I created new query and according to your requirement

SQL
```SELECT ItemName, SUM(tb1total-tb2total) AS DIFF
from
(
select ItemName,sum(Quantity) as tb1total,0 as tb2total
from Table1 group by ItemName
union all
select ItemName,0 as tb1total, sum(Quantity) as tb2total
from Table2 group by ItemName
) t
group by ItemName```

Magic Wonder 16-Sep-14 7:16am
Thats the Bingo...5
Gihan Liyanage 16-Sep-14 7:18am
Thanx again.. !!!
osmo33 16-Sep-14 8:18am
thanks for help..........

## Solution 3

i prefer using cte instead of temporary table

SQL
```; with table1grouped as (
SELECT ItemName,Sum(Quantity) as Quantity
FROM table1
GROUP BY itemName
), table2grouped as (
SELECT ItemName,Sum(Quantity) as Quantity
FROM table2
GROUP BY itemName
)
SELECT ItemName, (t1g.Quantity - ISNULL(t2g.Quantity,0)) as Quantity
FROM table1grouped t1g
LEFT JOIN table2grouped t2g on t1g.ItemName = t2g.ItemName```

v3
nrgjack 17-Sep-14 2:40am
Why downvoting without expleaning the reason guys?
That's not the spirit of codeproject...

## Solution 5

SQL
```select  sub.ItemName, SUM(sub.Quatity) from (select Table1.ItemName ,SUM(Table1.Quantity)Quatity from Table1
group by  Table1.ItemName
union
select Table2.ItemName ,SUM(Table2.Quantity)*-1 Quatity from Table2
group by  Table2.ItemName) sub group by ItemName```

[no name] 16-Sep-14 7:31am
for those who down vote my answer have you ever try it , have you check the performance
Note for second union values will be by minus SUM(Table2.Quantity)*-1
osmo33 16-Sep-14 7:54am
thanks mhegazy94;

## Solution 1

Hi,

Check this...

SQL
```CREATE PROC PR_TEST_PROC
AS
BEGIN

CREATE TABLE #tmp_TBL1
(
itemName varchar(100),
Qty int
)

INSERT INTO #tmp_TBL1
SELECT ItemName,Sum(Quantity)
FROM table1
group by itemName

--Uncomment below if you want to check output of table
--SELECT * from #tmp_TBL1

CREATE TABLE #tmp_TBL2
(
itemName varchar(100),
Qty int
)

INSERT INTO #tmp_TBL2
SELECT ItemName,Sum(Quantity)
FROM table2
group by itemName

--Uncomment below if you want to check output of table
--SELECT * from #tmp_TBL2

--mentioned alias--
SELECT t1.ItemName, t1.Qty - t2.Qty as Qty
from #tmp_TBL1 t1,#tmp_TBL2 t2
WHERE t1.itemName = t2.ItemName

END```

Cheers

v4
osmo33 16-Sep-14 5:27am
this is giving following error

Column "ItemName" in field list is ambiguous...
Magic Wonder 16-Sep-14 5:31am
Check updated solution. Forgot to mention alias.
osmo33 16-Sep-14 5:31am
I AM TRYING THAT

SELECT t1.ItemName, t1.Quantity - t2.Quantity as Qty
from table1 t1,table2 t2
WHERE t1.ItemName = t2.ItemName;

BUT IT GIVING UNUSED VALUE..
Magic Wonder 16-Sep-14 5:34am
Check what values you are getting in

#tmp_tbl1 & #tmp_TBL2 .....by selecting them.
Gihan Liyanage 16-Sep-14 5:47am
Whats the point of creating temp tables ????? See my answer Magic Wonder. Its can easily done by using Union