SQL Server: combine two values in the same row

I have the following union all results

ID  Name     Date         Team         Total#1     Total#2
1   Test_1   4/25/2011    Team_1         110          0
1   Test_1   4/25/2011    Team_2         20           0
1   Test_1   4/25/2011    Team_3         170          0
1   Test_1   4/25/2011    Team_1         0            151
1   Test_1   4/25/2011    Team_3         0            98

I am looking to combine the results for each team. There are some duplicate team names with different totals and there are some unique teams names with only one total value.

ID  Name     Date         Team         Total#1     Total#2
1   Test_1   4/25/2011    Team_1         110          15开发者_运维知识库1
1   Test_1   4/25/2011    Team_2         20           0
1   Test_1   4/25/2011    Team_3         170          98

Any thoughts?

SELECT Id, Name, Date, Team, SUM([Total#1]) [Total#1], SUM([Total#2]) [Total#2]
FROM YourUnionResult
GROUP BY Id, Name, Date, Team

Just use aggregate functions and GROUP BY:

SELECT ID, Name, Date, Team, SUM(Total#1) as Total1, Sum(Total#2) as Total2
FROM Mytable
WHERE ...
GROUP BY ID, Name, Date, Team

doesn't this work?

select id,name,date,team,sum(Total#1) as Total#1,sum(Total#2) as Total#2
from Your table
group by id,name,date,team

Make the select... union select... a subquery, and the "outer" query issues a group by against it.

Assuming for some reason standard Grouping isn't working for you could do this

SELECT t1.id, 
       t1.name, 
       t1.DATE, 
       t1.team, 
       t1.total_1, 
       t2.total_2 
FROM   (SELECT id, 
               name, 
               DATE, 
               team, 
               somecomplicatedcalculation total_1 
        FROM   your table) t1 
       LEFT JOIN (SELECT id, 
                         name, 
                         DATE, 
                         team, 
                         somecomplicatedcalculation total_2 
                  FROM   your table) t2 
         ON t1.id = t2.id
            and t1.team = t2.team

You could also do the same with CTE's

WITH t1 
     AS (SELECT id, 
                name, 
                DATE, 
                team, 
                somecomplicatedcalculation total_1 
         FROM   your table), 
     t2 
     AS (SELECT id, 
                name, 
                DATE, 
                team, 
                somecomplicatedcalculation total_2 
         FROM   your table) 
SELECT * 
FROM   t1 
       INNER JOIN t2 
         ON t1.id = t2.id 
            and t1.team = t2.team

OR grouping on the union would work to

SELECT id, 
       name, 
       DATE, 
       team, 
       SUM(total_1), 
       SUM(total_2) 
FROM   (SELECT id, 
               name, 
               DATE, 
               team, 
               somecomplicatedcalculation total_1, 
               0                          AS total_2 
        FROM   yourtable 
        UNION ALL 
        SELECT id, 
               name, 
               DATE, 
               team, 
               0                          AS total_1, 
               somecomplicatedcalculation total_2 
        FROM   yourtable) total 
GROUP BY id, 
       name, 
       DATE, 
       team