create table #t1(
  idgroup1 int
 ,idgroup2 int
 ,idproduct int
 ,value float
)
create table #t2(
  idgroup2 int
 ,idproduct int
)

insert into #t1 values(1,8,2,10)
insert into #t1 values(1,8,2,8)
insert into #t1 values(1,8,3,15)
insert into #t1 values(2,9,1,10)
insert into #t1 values(2,9,2,8)
insert into #t1 values(3,7,3,15)
insert into #t1 values(3,7,3,8)
insert into #t1 values(3,7,1,15)

insert into #t2 values(6,1)
insert into #t2 values(6,2)
insert into #t2 values(6,3)
insert into #t2 values(7,1)
insert into #t2 values(7,2)
insert into #t2 values(7,3)
insert into #t2 values(8,1)
insert into #t2 values(8,2)
insert into #t2 values(9,1)
insert into #t2 values(9,2)
insert into #t2 values(9,3)

select * from #t1
select * from #t2

select idgroup1 
 ,idgroup2 
 ,idproduct
 ,sum(value) as value
from
(select idgroup1 
 ,idgroup2 
 ,idproduct 
 ,sum(value) as value 
 from #t1
 group by idgroup1 
 ,idgroup2 
 ,idproduct 
 
 union

select t1.idgroup1 
 ,t1.idgroup2 
 ,#t2.idproduct 
 ,t1.value
from
(select idgroup1 
 ,idgroup2 
 ,idproduct 
 ,0 as value
 from #t1
 group by idgroup1 
 ,idgroup2 
 ,idproduct) t1 right join #t2 on t1.idgroup2 = #t2.idgroup2) t
 group by idgroup1 
 ,idgroup2 
 ,idproduct
 
drop table #t1
drop table #t2