Count of columns - 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 2005 Forums

Transact-SQL (2005)

Count of columns

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-04-22 : 19:32:02

Hi all,
My input table is having 4 records

Col1 Col2 Col3 Col4
215 220 225 226
210 null null null
213 223 null null
430 423 524 null

Now i want o/p as count of col1, col2, col3 and col4 in Col5 like shown in below

Col1 Col2 Col3 Col4 Col5
215 220 225 226 4
210 null null null 1
213 223 null null 2
430 423 524 null 3

Can any one help me in this.

-Thanks N Regards,
Chinna.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-22 : 20:49:06

[code]Select col1,col2,col3,Count(col1)+Count(col2)+Count(col3)
from table
Group by col1,col2,col3[/code]

notmyrealname

98 Posts

Posted - 2009-04-22 : 21:27:29

SELECT Col1, Col2, Col3, Col4, ISNULL(Col1 / Col1, 0) + ISNULL(Col2 / Col2, 0) + ISNULL(Col3 / Col3, 0) + ISNULL(Col4 / Col4, 0) AS Col5
FROM Table

maeenul
Starting Member

20 Posts

Posted - 2009-04-23 : 12:57:19

jswota is right. The way you want to count is not the sql count. SQL count() works for rows, but you want to count non null column values.

-----------------------
maeenul
http://www.programmingsolution.net
http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources