count of a value from different column by column wise count



  • I have a table with columns having values 0 and 1 like give below

    Column A = 0,1,0 Column B = 0,1,1 Column C = 1,1,1

    I need to count occurrence of "1" from each column. (eg. A = 1, B = 2, C = 3). Kindly guide me to get the count based on column count.



  • Let's use a string trick. Try this:

    SELECT LENGTH("0,1,1") - LENGTH(REPLACE("0,1,1", "1", ""));
    

    It returns 2.

    So:

    SELECT LENGTH(A) - LENGTH(REPLACE(A, "1", "")) AS A_count,
           LENGTH(B) - LENGTH(REPLACE(B, "1", "")) AS B_count,
           LENGTH(C) - LENGTH(REPLACE(C, "1", "")) AS C_count
        FROM ...
    

    If you had a bit string, see BIT_COUNT(). If you have a character string with 0s and 1s, BIT_COUNT(convert(my_str, 2, 10));

    Consider writing a Stored Function to encapsulate the tricky code.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2