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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Sum of 4 values in separate colums
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Eithne
Starting Member

8 Posts

Posted - 03/30/2005 :  05:45:28  Show Profile
I have four colums in a table and i need the sum of the four colums.

Can i do this in one sql statement as the sum function only works for the sum of the individual columns.

LarsG
Constraint Violating Yak Guru

Sweden
284 Posts

Posted - 03/30/2005 :  06:07:00  Show Profile

sum(coalesce(c1,0)+coalesce(c2,0)+coalesce(c3,0)+coalesce(c4,0))

Go to Top of Page

ijprasad
Starting Member

India
29 Posts

Posted - 03/30/2005 :  06:16:23  Show Profile  Send ijprasad an AOL message
you can add calculated column to calculate sum of 4 columns automatically by sql server

Inderjeet
Go to Top of Page

Eithne
Starting Member

8 Posts

Posted - 03/30/2005 :  06:17:18  Show Profile
is this correct:

select sum(POval1),sum(POval2),sum(POval3),sum(POval4) from projects where project_code = '123456'
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 03/30/2005 :  06:59:36  Show Profile
Syntax is right....try it to see if the result is what you want.
Go to Top of Page

Eithne
Starting Member

8 Posts

Posted - 03/30/2005 :  07:24:38  Show Profile
the above statement only gives individual column totals, i need the total of all columns together, is the below the correct version of the first reply suggestion?

select sum(POval1(c41,0)+ POval2(c42,0) + POval3(c43+0) + POval4(c44,0)) from projects where project_code = 'ECP20362'
Go to Top of Page

mr_mist
Grunnio

United Kingdom
1870 Posts

Posted - 03/30/2005 :  07:35:55  Show Profile  Visit mr_mist's Homepage
No.

Leave the coalesce function where it is in the statement and substitute your four column names for the c1,c2,c3 and c4.

-------
Moo. :)
Go to Top of Page

Eithne
Starting Member

8 Posts

Posted - 03/30/2005 :  07:44:30  Show Profile
Great it works, thanks everyone for your help!
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 03/30/2005 :  20:26:35  Show Profile
If POVal returns a Null value, your results may not be what you want...

FYI


HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000