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 |
|
hai
Yak Posting Veteran
84 Posts |
 Posted - 2007-05-24 : 12:32:41
|
| Hi all,The following is the problem I'm trying to resolve. I have the following table columnum with these values:t1|t2|t3|t4|t5 |1 | |1 | 1 |1 | | |1 | |2 | |3 | | | |5 | | | |1What I like it the total count of each columun first appear: egt1=1 because t1 row 2 have value of 1t2=1 because t2 row 1 have value of 1t3=1 because t3 row 3 have value of 2t4=0 t5=2 because nothing in front of last 2 rows thanks |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-24 : 13:16:06
|
| What column are you going to use to order your rows to determine which records are "first"?Also, what is the primary key for your table?e4 d5 xd5 Nf6 |
 |
|
|
kathik
Starting Member
3 Posts |
Posted - 2007-05-24 : 17:02:46
|
| This will work, substitute your actual table name for "test":select count(t1) as t1, count(case when t1 is null then t2 else null end) as t2,count(case when t1 is null and t2 is null then t3 else null end) as t3,count(case when t1 is null and t2 is null and t3 is null then t4 else null end) as t4,count(case when t1 is null and t2 is null and t3 is null and t4 is null then t5 else null end) as t5from testKathi |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-25 : 11:13:25
|
| kathik, look up the COALESCE() function in Books Online. It will save you much typing and debugging.e4 d5 xd5 Nf6 |
|
|
|
|
|
|
count tricky problem.