--create table test1
--(id int,a int)
--insert into test1 values
--(1,10),(1,5),(1,20),
--(2,10),(2,15),(2,25)--create table test2
--(id int,b int)
--insert into test2 values
--(1,3),(1,5),(1,7),
--(1,9),(2,24),(2,23)
SELECT id,SUM(CASE WHEN TB='a' THEN suma ELSE 0 END) suma ,SUM(CASE WHEN TB='b' THEN suma ELSE 0 END) sumb,c=SUM(CASE WHEN TB='a' THEN suma ELSE 0 END)-SUM(CASE WHEN TB='b' THEN suma ELSE 0 END)
FROM (
SELECT id,SUM(a)suma ,'a' tb
FROM test1
GROUP BY id
UNION ALL
SELECT id,SUM(b)sumb ,'b'
FROM test2
GROUP BY id)c
GROUP BY id/*
id suma sumb c
----------- ----------- ----------- -----------
1 35 24 11
2 50 47 3
*/
--(id int,a int)
--insert into test1 values
--(1,10),(1,5),(1,20),
--(2,10),(2,15),(2,25)--create table test2
--(id int,b int)
--insert into test2 values
--(1,3),(1,5),(1,7),
--(1,9),(2,24),(2,23)
SELECT id,SUM(CASE WHEN TB='a' THEN suma ELSE 0 END) suma ,SUM(CASE WHEN TB='b' THEN suma ELSE 0 END) sumb,c=SUM(CASE WHEN TB='a' THEN suma ELSE 0 END)-SUM(CASE WHEN TB='b' THEN suma ELSE 0 END)
FROM (
SELECT id,SUM(a)suma ,'a' tb
FROM test1
GROUP BY id
UNION ALL
SELECT id,SUM(b)sumb ,'b'
FROM test2
GROUP BY id)c
GROUP BY id/*
id suma sumb c
----------- ----------- ----------- -----------
1 35 24 11
2 50 47 3
*/
with t1 as (
select id,SUM(A) suma
from test1
group by id
)
,t2 as (
select id,SUM(b) sumb
from test2
group by id
)
select
ISNULL(t1.id,t2.id)id
,ISNULL(t1.suma,0)-ISNULL(t2.sumb,0) c
from t1 full join t2 on t1.id=t2.id
suma,
sumb,
suma - sumb c
from
(
select ID,SUM(a) as suma
from test1
group by id
)t1
inner join
(
select ID,SUM(b) as sumb
from test2
group by id
) t2
on t1.id = t2.id
/*
id suma sumb c
1 35 24 11
2 50 47 3
*/