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

Thanks!

Joe

"Joe User" <joe@user.com> wrote in message
news:c4s5vp$6je$1@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

Excellent!

Thanks!

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

TIA



"Simon Hayes" <sql@hayes.ch> wrote in message
news:4071a1c0$1_3@news.bluewin.ch...
>
> "Joe User" <joe@user.com> wrote in message
> news:c4s5vp$6je$1@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
>
>

"Joe User" <joe@user.com> wrote in message
news:c4sa4g$c6p$1@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

>> 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;