This discussion is archived
5 Replies Latest reply: May 3, 2013 11:10 AM by 1006878 RSS

Count Columns in a row and Logic Relates

1006878 Newbie
Currently Being Moderated
I need some help in oracle SQL,


Student(unique) has five races in a table

I need a select statement with student_id - 1 column


I need to count the number columns with values 'Yes' in them

the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn


The 3rd column should contain the max of the race having 'yes' in them

i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column

Can some one help me in this
  • 1. Re: Count Columns in a row and Logic Relates
    John Stegeman Oracle ACE
    Currently Being Moderated
    In all seriousness, the data model is wrong. If you broke out the races into a separate table (student_id, race_id as key), the SQL becomes trivial to write.
  • 2. Re: Count Columns in a row and Logic Relates
    sukhijank Explorer
    Currently Being Moderated
    Hi,

    As pointed out by John, the datamodel is wrong and keeping it as separate table with (student_id, race_id) will be ideal. But, if you still want to use your existing data model, you can use something like
    WITH students AS (SELECT 1 student_id,
                             'Yes' race1,
                             'No' race2,
                             'Yes' race3,
                             'No' race4,
                             'Yes' race5
                        FROM DUAL
                      UNION ALL
                      SELECT 2 student_id,
                             'Yes' race1,
                             'No' race2,
                             'Yes' race3,
                             'Yes' race4,
                             'No' race5
                        FROM DUAL)
    SELECT student_id,
           DECODE (race1, 'Yes', 1, 0) 
        + DECODE (race2, 'Yes', 1, 0)
        + DECODE (race3, 'Yes', 1, 0)
        + DECODE (race4, 'Yes', 1, 0)
        + DECODE (race5, 'Yes', 1, 0) count_race,
           CASE 
             WHEN race5 = 'Yes' THEN 5 
             WHEN race4 = 'Yes' THEN 4 
             WHEN race3 = 'Yes' THEN 3 
             WHEN race2 = 'Yes' THEN 2 
             WHEN race1 = 'Yes' THEN 1 
             ELSE 0 
           END max_race
      FROM students
    Thanks,
    sukhijank
  • 3. Re: Count Columns in a row and Logic Relates
    1006154 Newbie
    Currently Being Moderated
    Hi~. This is unpivot version (from 11g). Thanks~!
    WITH students AS (
                     SELECT 1 student_id ,
                            'Yes' race1 ,
                            'No' race2 ,
                            'Yes' race3 ,
                            'No' race4 ,
                            'Yes' race5
                     FROM   dual
                     UNION  ALL
                     SELECT 2 student_id ,
                            'Yes' race1 ,
                            'No' race2 ,
                            'Yes' race3 ,
                            'Yes' race4 ,
                            'No' race5
                     FROM   dual
         )
    SELECT student_id ,
           COUNT( * ) ,
           MAX( rnum )
    FROM   (
            SELECT student_id ,
                   result ,
                   race ,
                   ROW_NUMBER( ) over( PARTITION BY student_id
                                       ORDER     BY race ) rnum
            FROM   students unpivot( result
                             FOR race IN ( "RACE1" , "RACE2" , "RACE3" , "RACE4" , "RACE5" ) )
           )
    WHERE  result='Yes'
    GROUP  BY student_id
    Edited by: seankim on 2013. 5. 2 오후 11:45
  • 4. Re: Count Columns in a row and Logic Relates
    1006878 Newbie
    Currently Being Moderated
    Thankk you
  • 5. Re: Count Columns in a row and Logic Relates
    1006878 Newbie
    Currently Being Moderated
    Thankl you, it worked

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points