Count Positive/Negative Numbers in a Row (T-SQL)



CREATE TABLE [dbo].[TableName](
	[col1] [int] NOT NULL,
	[col2] [int] NOT NULL,
	[col3] [int] NOT NULL,
	[col4] [int] NOT NULL,
	[col5] [int] NOT NULL,
	[Id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[TableName] ON 

GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-15, 12, 9, 5, -7, 1)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-3, 5, -15, 6, -16, 2)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (10, -14, 8, 17, -16, 3)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-11, -10, 5, 2, -4, 4)
GO
SET IDENTITY_INSERT [dbo].[TableName] OFF
GO

SELECT [col1],[col2],[col3],[col4],[col5],pCount,nCount
FROM   (SELECT
       --row_number() over(order by newid()) rn, 
       *
        FROM   TableName) src
       CROSS apply (SELECT SUM (CASE
                                  WHEN col >= 0 THEN 1
                                  ELSE 0
                                END) pCount
                    FROM   (VALUES ([col1]),
                                   ([col2]),
                                   ([col3]),
                                   ([col4]),
                                   ([col5])) d (col)) c1
       CROSS apply (SELECT SUM (CASE
                                  WHEN col < 0 THEN 1
                                  ELSE 0
                                END) nCount
                    FROM   (VALUES ([col1]),
                                   ([col2]),
                                   ([col3]),
                                   ([col4]),
                                   ([col5])) d (col)) c2 

--http://social.msdn.microsoft.com/Forums/mr/transactsql/thread/9178b50f-6094-4460-83d2-9ec925d0b4f4

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s