A nice function for adding values

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 2005 Forums

Transact-SQL (2005)

A nice function for adding values

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-07-03 : 06:06:55

Hi, I'm returning records with a select statement, and one of the columns holds the number of free spaces.

So;

ID Freespaces
12 5
13 2
11 6
14 -1
18 0
17 6

I'm using a -1 to indicate that this field should not be shown on the website, a 0 will just show a 0.
Now, I want to give the total number of freespaces and it should return (5 + 2 + 6 + 0 + 6) = 19

So it should ignore the -1 values.

Any idea how this can be done?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-03 : 06:29:58

select sum(case when Freespaces > 0 then Freespaces else 0 end) as Freespaces
from MyTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-03 : 06:40:52

select sum(Freespaces) as Freespaces
from MyTable
where Freespaces > 0

Peter Larsson
Helsingborg, Sweden

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-07-03 : 06:58:55

Why didn't I think of that....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources