none
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.

    Monday, November 19, 2012 4:54 AM

Answers

  • 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 Rishabh K Monday, November 19, 2012 6:02 AM
    • Marked as answer by SQLSPUser 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 Rishabh K Monday, November 19, 2012 5:12 AM
    Monday, November 19, 2012 5:09 AM
  • 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. 

    • Edited by SQLSPUser Monday, November 19, 2012 5:56 AM
    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 Rishabh K Monday, November 19, 2012 6:02 AM
    • Marked as answer by SQLSPUser 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