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.
| Author |
Topic |
|
danbweb
Starting Member
7 Posts |
 Posted - 2007-08-16 : 21:52:16
|
| Hi - Have a table full of records as follows:ID M1 M2 M3 M4 M5 M6------------------------------1 DD1 DD32 AA1 AA2 AA3..........What I want to do is for each record add up the number of columns (M1 to M6) that are not null. So for #1, it would return 2. For #2, it would return 3, etc.I tries using Count and sum, but being new to SQl, didnt get very farThanks for any help,Dan B |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-16 : 22:30:18
|
count = (case when M1 is null then 0 else 1 end) +(case when M2 is null then 0 else 1 end) +(case when M3 is null then 0 else 1 end) + . . .
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
danbweb
Starting Member
7 Posts |
Posted - 2007-08-17 : 10:55:39
|
| Bam! Works well, but it leads to another question - How can I now use this count in a condition statement such as:If count = 5 do somethingThe above doesn't work and setting something like below doesn't either:select@count = count = (case when (M1 = '0' or M1 is Null) then 0 else 1 end) +(case when (M2 = '0' or M2 is Null) then 0 else 1 end) + |
|
|
|
danbweb
Starting Member
7 Posts |
Posted - 2007-08-17 : 10:57:57
|
| I figured it out - thanks for your help |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-08-17 : 11:12:19
|
where (case when M1 is null then 0 else 1 end) +(case when M2 is null then 0 else 1 end) +(case when M3 is null then 0 else 1 end) + . . . = 5or use a derived tableselect *from( select count = (case when . . ) + () + . . . from youtable) awhere count = 5
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
Count question