469,272 Members | 1,438 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

Count of Columns <> 0

How would you count the number of columns with a value not equal to 0 for
each row in a table?

Thanks!

Joe
Jul 20 '05 #1
4 3595

"Joe User" <jo*@user.com> wrote in message
news:c4**********@tribune.mayo.edu...
How would you count the number of columns with a value not equal to 0 for
each row in a table?

Thanks!

Joe


Here's one way:

select PrimaryKeyColumn,
case when col1 = 0 then 0 else 1 end +
case when col2 = 0 then 0 else 1 end +
case when col3 = 0 then 0 else 1 end +
...
case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
from
dbo.MyTable
Simon
Jul 20 '05 #2
Excellent!

Thanks!

Next question.....
How does someone relatively new to tsql learn this sort of thing?

TIA

"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40**********@news.bluewin.ch...

"Joe User" <jo*@user.com> wrote in message
news:c4**********@tribune.mayo.edu...
How would you count the number of columns with a value not equal to 0 for each row in a table?

Thanks!

Joe


Here's one way:

select PrimaryKeyColumn,
case when col1 = 0 then 0 else 1 end +
case when col2 = 0 then 0 else 1 end +
case when col3 = 0 then 0 else 1 end +
...
case when coln = 0 then 0 else 1 end as 'NonZeroColumns'
from
dbo.MyTable
Simon

Jul 20 '05 #3

"Joe User" <jo*@user.com> wrote in message
news:c4**********@tribune.mayo.edu...
Excellent!

Thanks!

Next question.....
How does someone relatively new to tsql learn this sort of thing?

TIA


<snip>

Get a good book or two - there are some suggestions here:

http://vyaskn.tripod.com/sqlbooks.htm

But don't forget Books Online itself - it's very helpful to read through the
TSQL reference part. I don't mean read every word (unless you have a lot of
time on your hands...), but it helps to have an idea of what's available in
the language. Even if you only vaguely remember what a keyword does, or if
you only remember the name, you can always look it up. The list of functions
is another useful page to review, for the same reason. The
SELECT/INSERT/UPDATE/DELETE entries are very important, as are the CREATE
XXXX entries - all of them are linked to lots of related information, so you
can go into as much detail as you want.

Simon
Jul 20 '05 #4
>> How would you count the number of columns with a value not equal to
0 for each row in a table? <<

SELECT keycol,
ABS(SIGN(col1)) +
ABS(SIGN(col2)) +
ABS(SIGN(col3)) + .. AS non_zero_tally
FROM Foobar;
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Donald Firesmith | last post: by
reply views Thread by - | last post: by
3 posts views Thread by ajay2552 | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.