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;
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;