Sum 2 fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum 2 fields

Author

Topic

TennSQLguy
Starting Member

3 Posts

Posted - 2012-03-06 : 17:19:01

Hello!

I have a field - bank code = VRKSS that has an amount for year 2009 and one for 2010.

I want to run a query to pull out all the accounts with this code (VRKSS) and sum the 2 years together. I have been playing around with this for a couple hours with no luck.

Basically my data looks like this:

Bank_Amt = 10.00 and Bank.Bank_Code = VRKSS and Bank.Bank_Year = 2009
Bank_Amt = 20.00 and Bank.Bank_Code = VRKSS and Bank.Bank_Year = 2010

I would like the Bank_Amt total as: 30.00

So my output would be:
Bank_Amt 30.00, Bank_Code = VRKSS

Does this make sense? So sorry to be confusing. Any help is much appreciated!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-06 : 17:22:05

SELECT Bank_Code, SUM(Bank_Amt) Bank_Amt
FROM Bank
WHERE Bank_Code='VRKSS' AND Bank_Year IN (2009,2010)
GROUP BY Bank_Code

TennSQLguy
Starting Member

3 Posts

Posted - 2012-03-07 : 10:30:00

Thank You! I should have included my current query which will give me all the data, however, I want to combine the 2 rows of data (different TERM_CODE which is just a code for the year - stored as a string) - So - can I ask again for a little assistance? Thanks so much.

select distinct
(SPIRIT_ID),
SPIRIT_LAST_NAME,
SPIRIT_FIRST_NAME,
BANK_ACCT_CODE,
BANK_OFFER_AMT,
BANK_TERM_CODE

from SPIRIT,
BANK

where
BANK_OFFER_AMT > '0'

and BANK_TERM_CODE='&Term_DATE'
and BANK_ACCT_CODE in 'VRKSSS'
and SPIRIT_CHANGE_IND IS NULL
and SPIRIT.SPIRIT_pidmas = BANK.BANK_pidmas

UNION

select distinct
(SPIRIT_ID),
SPIRIT_LAST_NAME,
SPIRIT_FIRST_NAME,
BANK_ACCT_CODE,
BANK_OFFER_AMT,
BANK_TERM_CODE

from SPIRIT,
BANK

where
BANK_OFFER_AMT > '0'

and BANK_TERM_CODE='&Term_DATE'
and BANK_ACCT_CODE like VRKSSS'
and SPIRIT_CHANGE_IND IS NULL
and SPIRIT.SPIRIT_pidmas = BANK.BANK_pidmas

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 10:36:32

for us to really help you, please provide the DDL of your table(s), sample data in DML Form and expected results

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

TennSQLguy
Starting Member

3 Posts

Posted - 2012-03-08 : 17:23:14

Each table has a pidmas and that is how they are linked (primary key between the two tables).

SPIRIT_LAST_NAME = 'Smith'
SPIRIT_FIRST_NAME = 'John'
BANK_ACCT_CODE = 'VRBKSS'
BANK_OFFER_AMT = '250'
BANK_TERM_CODE = '201180'

Does that make sense?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-08 : 17:37:58

Nope. How about some samples of the data and what the results should be?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources