# select count of number of 1's and 0's in a specific row

### Question

• Hi All,

I am trying to get a count of number of 1's and 0's in a specific row. In the below table for row1 count of 1's in 3 and 0's is 2. Similarly in row2 count pf 1's is 4 and 0's is 2. Is there a way that I can get this with sql query?

1 1 0 1 0
1 0 1 1 1
1 1 1 1 0
0 0 1 0 0
0 0 1 1 0
1 0 1 0 0
0 0 0 1 0
1 1 1 0 0
0 1 0 1 1

Monday, November 19, 2012 4:54 AM

• plz try this

`select c1+c2+c3+c4+c5 as 'Count of 1' , 5-(c1+c2+c3+c4+c5) as 'Count of 0' from test`

• Proposed as answer by Monday, November 19, 2012 6:02 AM
• Marked as answer by Monday, November 19, 2012 6:05 AM
Monday, November 19, 2012 6:01 AM

### All replies

• PLease find the correct input below:

Monday, November 19, 2012 4:58 AM
• Pls post your table structure.Meanwhile try to concatenate all the columns as a single value and then use LEN and REPLACE to calculate the number of 1's or 0's like this : LEN(<Concatvalue>) - LEN(REPLACE(<concatvalue>,'1',''))  AS Number of 1's

Thanks and regards, Rishabh K

• Edited by Monday, November 19, 2012 5:12 AM
Monday, November 19, 2012 5:09 AM

CREATE TABLE [dbo].[Test](

[c1] [int] NOT NULL,
[c2] [int] NOT NULL,
[c3] [int] NOT NULL,
[c4] [int] NOT NULL,
[c5] [int] NOT NULL
) ON [PRIMARY]

GO

I have to script for a count of 0's and count 1's in every record.

Monday, November 19, 2012 5:54 AM
• plz try this

`select c1+c2+c3+c4+c5 as 'Count of 1' , 5-(c1+c2+c3+c4+c5) as 'Count of 0' from test`

• Proposed as answer by Monday, November 19, 2012 6:02 AM
• Marked as answer by Monday, November 19, 2012 6:05 AM
Monday, November 19, 2012 6:01 AM
• ```INSERT INTO TEST
SELECT 1,0,0,1,1
UNION ALL
SELECT 1,1,1,1,1
UNION ALL
SELECT 0,0,0,0,0

;WITH CTE AS
(
SELECT CAST(C1 AS Char(1)) + CAST(C2 AS CHAR(1)) + CAST(C3 AS CHAR(1)) + CAST(C4 AS CHAR(1)) + CAST(C5 AS CHAR(1)) AS ConactValue
FROM dbo.TEST
)

SELECT
ConactValue,
LEN(ConactValue) - LEN(REPLACE(ConactValue,'1','')) AS Number_of_1s,
LEN(ConactValue) - LEN(REPLACE(ConactValue,'0','')) AS Number_of_0s
FROM CTE
```

Thanks and regards, Rishabh K

Monday, November 19, 2012 6:02 AM
• Thanks for your help Guys. I appreciate it.
Monday, November 19, 2012 6:06 AM