5 Replies Latest reply: May 3, 2013 1:10 PM by 1006878 RSS

    Count Columns in a row and Logic Relates

    1006878
      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
          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
            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
              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
                Thankk you
                • 5. Re: Count Columns in a row and Logic Relates
                  1006878
                  Thankl you, it worked