Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: how to count the columns value from a row

    Hi All,

    I want to count the column value from a row.

    My query is: i have a table

    Recid A B C D E

    1 Y Y N NA N
    2 N N N Y NA


    I want to count Y from each row.


    Kindly do the needfull.


    Regards,
    Divya

  2. #2
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Select

    Recid,
    (
    (Case When A = 'Y' Then 1 Else 0 End)
    + (Case When B = 'Y' Then 1 Else 0 End)
    + (Case When C = 'Y' Then 1 Else 0 End)
    + (Case When D = 'Y' Then 1 Else 0 End)
    + (Case When E = 'Y' Then 1 Else 0 End)
    ) as Count_Y

    From Sample_Table


    If the number of columns are more (i.e., >10) then I think that the above query is not a good practice.

    I assume its good to reorganize the table design in such cases.

  3. #3
    Join Date
    Dec 2009
    Posts
    2
    ya coluns are >10 and i need to count N,Y,NA. from each row.

    Plz help me

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by divyain View Post
    I want to count Y from each row.
    Code:
    SELECT Recid
         , LEN(A+B+C+D+E+...) -
           LEN(REPLACE(A+B+C+D+E+...,'Y','')) 
             AS  number_of_Ys
      FROM daTable
    kindly do the needful and redesign your table so that this nonsense won't be necessary

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Hi Divya,

    Check the CONTAINS() and CONTAINSTABLE() functions in SQL 2008. Those might help you. I'm not having SQL 2008 version and unable to use those functions.

    Both the above functions are having multiple column search options.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •