2

This query is meant to replace another that uses nested subqueries and performs accordingly. As it is now, it does return the correct results for each column, but will return a row that has the value for that column, nulls for the rest, then another row with the second value in the second column and nulls for the rest, and so on. Making it a "select distinct" helps bust it down to just the 5 rows for the 5 values, but I'd like a single row result that gives me:

pidm  code    date       sci   eng   math   soc   fl
119   456456  14-JUL-14  89.1  92.3  82     90.25 83.67

Instead of what I get now:

pidm  code    date       sci   eng   math   soc   fl
119   456456  14-JUL-14  89.1  null  null   null  null
119   456456  14-JUL-14  null  null  82     null  null
119   456456  14-JUL-14  null  92.3  null   null  null
119   456456  14-JUL-14  null  null  null   null  83.67
119   456456  14-JUL-14  null  null  null   90.25 null

I'm not all that familiar with Oracle's windowing functionality, and though I get the basic idea of what it's supposed to accomplish, I can't quite figure out how to twist it into what I need here.

Query as it is now:

SELECT a.szrhstr_pidm AS PIDM, 
       a.szrhstr_hs_code AS HS_Code, 
       a.szrhstr_activity_date,
       SUM(CASE WHEN hsgpa_subj_code='SCI' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code ) / COUNT(CASE WHEN hsgpa_subj_code='SCI' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code) as "Science_GPA", 
       SUM(CASE WHEN hsgpa_subj_code='ENG' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code ) / COUNT(CASE WHEN hsgpa_subj_code='ENG' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code) as "English_GPA",
       SUM(CASE WHEN hsgpa_subj_code='MATH' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code ) / COUNT(CASE WHEN hsgpa_subj_code='MATH' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code) as "Math_GPA",
       SUM(CASE WHEN hsgpa_subj_code='SOC' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code ) / COUNT(CASE WHEN hsgpa_subj_code='SOC' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code) as "History_GPA",
       ROUND(SUM(CASE WHEN hsgpa_subj_code='FL' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code ) / COUNT(CASE WHEN hsgpa_subj_code='FL' THEN a.szrhstr_grade END) OVER (PARTITION BY hsgpa_subj_code ORDER BY hsgpa_subj_code), 2) as "ForeignLang_GPA"
FROM TT_STUDENT.TT_ST_SZRHSTR a
JOIN tt_student.tt_st_hsgpa_code ON a.szrhstr_tea_cse_code LIKE hsgpa_tea_code
WHERE NOT EXISTS 
  (SELECT * FROM tt_student.tt_st_hsgpa WHERE hsgpa_pidm = a.szrhstr_pidm and trunc(hsgpa_activity_date) >= trunc(a.szrhstr_activity_date))
  AND decode(REGEXP_INSTR (a.szrhstr_grade, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') = 'NUMBER'
  and szrhstr_pidm = 119;
John O
  • 4,863
  • 8
  • 45
  • 78

1 Answers1

2

Just use group by and aggregation functions. Something like this will probably work:

SELECT a.szrhstr_pidm AS PIDM, 
       a.szrhstr_hs_code AS HS_Code, 
       a.szrhstr_activity_date,
       AVG(CASE WHEN hsgpa_subj_code = 'SCI' THEN a.szrhstr_grade END as "Science_GPA", 
       AVG(CASE WHEN hsgpa_subj_code = 'ENG' THEN a.szrhstr_grade END) as "English_GPA",
       AVG(CASE WHEN hsgpa_subj_code = 'MATH' THEN a.szrhstr_grade END) as "Math_GPA",
       AVG(CASE WHEN hsgpa_subj_code = 'SOC' THEN a.szrhstr_grade END) as "History_GPA",
       ROUND(AVG(CASE WHEN hsgpa_subj_code = 'FL' THEN a.szrhstr_grade END), 2) as "ForeignLang_GPA"
FROM TT_STUDENT.TT_ST_SZRHSTR a JOIN
     tt_student.tt_st_hsgpa_code
     ON a.szrhstr_tea_cse_code = hsgpa_tea_code
WHERE NOT EXISTS 
  (SELECT * FROM tt_student.tt_st_hsgpa WHERE hsgpa_pidm = a.szrhstr_pidm and trunc(hsgpa_activity_date) >= trunc(a.szrhstr_activity_date))
  AND decode(REGEXP_INSTR (a.szrhstr_grade, '[^[:digit:]]'),0,'NUMBER','NOT_NUMBER') = 'NUMBER'
  and szrhstr_pidm = 119
GROUP BY a.szrhstr_pidm, a.szrhstr_hs_code, a.szrhstr_activity_date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I swear I've tried that. When I copy and paste yours, it works, but uncommenting group by on my own is failing. Thanks, appreciate the help. – John O Jul 21 '14 at 18:59
  • 1
    You have to change the functions in the select to aggregation functions. It won't work with analytic functions using the subject code for partitioning. – Gordon Linoff Jul 21 '14 at 19:03
  • Yeh, I just noticed that the windowing was removed entirely. I see why this works now, but I don't think I'd ever figured it out on my own. Thank you, I really learned something. – John O Jul 21 '14 at 19:05