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

Thanks a bunch for your help in advance.

Answer 1

PLease find the correct input below:

Answer 2

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

Answer 3

Thanks for the reply

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. 

Answer 4

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

Answer 5

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

Answer 6

Thanks for your help Guys. I appreciate it.