Solved

query question to add value of two rows together

Posted on 2008-06-13
3
1,844 Views
Last Modified: 2012-06-22
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      
0
Comment
Question by:ewang1205
  • 2
3 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 400 total points
ID: 21782716
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 400 total points
ID: 21782722
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 21783141
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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question