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
 Old Forums
 CLOSED - General SQL Server
 Sum of 4 values in separate colums

Author  Topic 

Eithne
Starting Member

8 Posts

Posted - 2005-03-30 : 05:45:28
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

284 Posts

Posted - 2005-03-30 : 06:07:00
[code]
sum(coalesce(c1,0)+coalesce(c2,0)+coalesce(c3,0)+coalesce(c4,0))
[/code]
Go to Top of Page

ijprasad
Starting Member

29 Posts

Posted - 2005-03-30 : 06:16:23
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 - 2005-03-30 : 06:17:18
is this correct:

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-03-30 : 06:59:36
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 - 2005-03-30 : 07:24:38
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

1870 Posts

Posted - 2005-03-30 : 07:35:55
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 - 2005-03-30 : 07:44:30
Great it works, thanks everyone for your help!
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-30 : 20:26:35
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
   

- Advertisement -