Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to extract the sum of max score two different codes

896976May 11 2012 — edited May 11 2012
It is there an easy way to do this

this query
 
 SELECT sortest_pidm,
              a.sortest_tesc_code ,
              a.sortest_test_date,
             A.sortest_test_score,
             SYSDATE,
             sortest_equiv_ind
                FROM saturn.spriden, saturn.sortest a, saturn.stvtesc
       WHERE     a.sortest_pidm = spriden_pidm
             AND stvtesc_code = a.sortest_tesc_code
             AND spriden_change_ind IS NULL
             AND a.sortest_tesc_code IN ('S01', 'S02')
             ---for test purposes
            AND sortest_pidm = 206866
             --AND spriden_id =  p_student_id  
             AND a.sortest_test_score =
                    (SELECT MAX (b.sortest_test_score)
                       FROM saturn.sortest b
                      WHERE a.sortest_tesc_code = b.sortest_tesc_code
                            AND a.sortest_pidm = b.sortest_pidm)
             AND NOT EXISTS
                        (SELECT 1
                           FROM saturn.sortest b
                          WHERE     sortest_tesc_code = b.sortest_tesc_code
                                AND a.sortest_pidm = b.sortest_pidm
                                AND b.sortest_tesc_code = 'S08');
Retrieves this
 
206866	S01	10/1/2011	650	5/11/2012 9:17:45 AM	N
206866	S02	6/4/2011	800	5/11/2012 9:17:45 AM	N
I need to be able to retrieve the sum of codes SO1 and S02 (Codes) from the same sortest_pidm (unique key)
Thank you
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 8 2012
Added on May 11 2012
7 comments
176 views