Sum in union of queries

Transact-SQL
1

Team,

I have a result set as shown below which i get after executing union of two queries.

ResultDataSet

myuser val

john  10
 
kim   20

ekij  30

john  60
 
kim   45

ekij  30

I want my final reult to be like this sum after grouping by name.

john 70

kim   65

ekij  60

What i did was used a derived table.

select mytable.myuser, sum(mytable.val) from (

myquery 1
union
my query 2

) mytable group by mytable.myuser

but result is not as expected it gives same out put as query1

Output of Union query

myuser val

john  10
 
kim   20

ekij  30

john  60
 
kim   45

ekij  30
2

Hi...

Try this:

Declare @mytable table (MyUser varchar(100), MyVal int) ;

Insert into @mytable
Select 'john', 10
Union ALL
Select 'kim', 20
Union ALL
Select 'ekij', 30
Union ALL
Select 'john', 60
Union ALL
Select 'kim', 45
Union ALL
Select'ekij', 30 ;

Select * from @mytable ;

Select Myuser, sum(Myval) from @mytable Group by MyUser ;