Help needed with CASE statement in SQL

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)

Help needed with CASE statement in SQL

Author

Topic

Sumeet
Starting Member

3 Posts

Posted - 2007-07-17 : 09:20:55

Select
CASE
When Column = 'OK to Contact' THEN @Count = @Count + 1

from table_name where condition.

---------
In the above querry I am having syantax error at the =
Can anybody please tell me how to increment value of the @Count variable after the THEN clause.

Any help, much appreciated.

Best Regards,
Sumeet.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 09:25:19

[code] SELECT
CASE
WHEN Column = 'OK to Contact' THEN @COUNT = @COUNT + 1 END[/code]

KH
[spoiler]Time is always against us[/spoiler]

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-07-17 : 11:05:30

Isn't that still going to generate a syntax error?
Try

SELECT @Count =
CASE 
  WHEN Column = 'OK to Contact' 
  THEN @Count + 1 
  ELSE @Count 
END

Clearly, if you're just returning the value of @Count, a slightly more sensible alternative would be:

SELECT SUM(CASE WHEN Column = 'OK to Contact' THEN 1 ELSE 0 END)
FROM table_name
WHERE condition

Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 11:10:08

Oops

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 11:41:18

SELECT @count = COUNT(*) FROM table_name
WHERE Column = 'OK to Contact'

Peter Larsson
Helsingborg, Sweden

Sumeet
Starting Member

3 Posts

Posted - 2007-07-18 : 02:06:24

Thanks a ton. I actually had to use three case statements within a single select. I modified the querry in your suggested way, and YAY!!!

Best Regards,
Sumeet.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources