Michael Valentine Jones (11/19/2012)
This should work also:
selectabs(sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)) as NegativeCount
sign(col1)+sign(col2)+sign(col3)+
sign(col4)+sign(col5)+5 as PositiveCount
from
MyTable
Have you tested it? You should try:
CREATE TABLE [dbo].[MyTable](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
[col5] [int] NOT NULL
) ON [PRIMARY]
insert MyTable
select 0,-0,0,0,0
union select -3,5,-15,6,0
union select -3,5,-15,6,-16
union select 10,-14,8,17,-16
union select -11,-10,5,2,-4
union select -6,-2,7,-13,8
union select -4,2,-6,7,19
union select -10,8,7,16,-14
union select 17,-17,-9,14,18
union select 7,19,15,12,-13
union select 7,19,15,12,-13
It's not so simple as appears at first glance, so check my version...;-)