Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
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


Please help to execute sql query for solving this problem
Thanks advance
Posted
Updated 15-Sep-14 22:21pm
v5
Comments
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    
this is sql question please help to solve it ........
Gihan Liyanage 16-Sep-14 6:24am    
See my new solution. I have checked it again. and its working fine.

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
 
Share this answer
 
Comments
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..........
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
 
Share this answer
 
v3
Comments
nrgjack 17-Sep-14 2:40am    
Why downvoting without expleaning the reason guys?
That's not the spirit of codeproject...
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
 
Share this answer
 
Comments
[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;
your query work fine..
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


Hope this will help you.

Cheers
 
Share this answer
 
v4
Comments
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900