query question to add value of two rows together

I like to have a query to add all the type A+B salary together for each yr_mon.  Help me on the query please.  Thanks.

Here is the table data.
yr_mon      type  salary      
2008_01      A           100      
2008_01      B            200    
2008_02      A            300      
2008_02      B            400      
2008_02      C            500      

Here is my result data.  
yr_mon        salary      
2008_01                 300      
2008_02               700      
ewang1205Asked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
if there is a mismatch, you'll want to use this...

select a.yr_mon, isnull(salsuma,0) + isnull(salsumb,0)
from
(
select yr_mon, salsuma = sum(salary)
from yourtable
where type = 'A'
group by yr_mon
) a
full outer JOIN
(
select yr_mon, salsumb = sum(salary)
from yourtable
where type = 'B'
group by yr_mon
) b ON a.yr_mon = b.yr_mon
0
 
chapmandewConnect With a Mentor Commented:
select a.yr_mon, salsuma + salsumb
from
(
select yr_mon, salsuma = sum(salary)
from yourtable
where type = 'A'
group by yr_mon
) a
INNER JOIN
(
select yr_mon, salsumb = sum(salary)
from yourtable
where type = 'B'
group by yr_mon
) b ON a.yr_mon = b.yr_mon
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Could either A or B be mssing?  

If not:

SELECT a.yr_mon, a.salary + b.salary AS salary
FROM tableData a
INNER JOIN tableData b ON a.yr_mon = b.yr_mon AND b.type = 'B'
WHERE a.type = 'A'

If B, only, could be missing:

SELECT a.yr_mon, a.salary + ISNULL(b.salary, 0) AS salary
FROM tableData a
LEFT OUTER JOIN tableData b ON a.yr_mon = b.yr_mon AND b.type = 'B'
WHERE a.type = 'A'


If A, only, could be missing, let me know, that's somewhat more coding, don't want to do it for no reason :-) .
0
All Courses

From novice to tech pro — start learning today.