sum function - 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

SQL Server 2008 Forums

Transact-SQL (2008)

sum function

Author

Topic

venkatch786
Starting Member

8 Posts

Posted - 2010-08-26 : 09:15:22

Hi,

I have 4 columns StdName Maths Physics Chemistry TotalMarks
these marks data type is varchar before.

First convert into float.
I want to sum these three columns values and display in last column
for each record.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 09:20:56

Why use FLOAT? Your scores (or marks) are integers?
And integers within a certain range?

Use TINYINT for scores/marks between 0 and 255
Use SMALLINT for scores/marks between -32768 and 32767
Use INT for scores/marks between -2147483648 and 2147483647
Use BIGINT for scores/marks between -9223372036854775808 and 9223372036854775807

Then, add a computed column to your table, like this
ALTER TABLE MyTableNameHere
ALTER COLUMN TotalMarks (ISNULL(Maths, 0) + ISNULL(Physics, 0) + ISNULL(Chemistry, 0) )

N 56°04'39.26"
E 12°55'05.63"

venkatch786
Starting Member

8 Posts

Posted - 2010-08-26 : 09:36:44

Hi Peso
Thanks for your responce.

we can't say marks in integer. some time it is in float also.
like 44.5 , 66.5, 77 Here we calculate half mark also.

and we want to use sum function. not alter column..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 09:41:47

Yes but you still have a range, right?
Let it be 0.0 to 100.0, or similar?
Then the maximum total score can be 300.0

Use DECIMAL(4, 1) as datatype.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-26 : 09:44:19

SELECT StdName, Maths, Physics, Chemistry, Maths + Physics + Chemistry AS TotalMarks
FROM YourTableNameHerePlease

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources