Scott Riggs Scott Riggs - 9 days ago 5
SQL Question

SQL Sum not working correctly with group by

I’m writing Oracle SQL for detailed and summary reports. My detailed report is finish with example output rows would be:

domain name, student name, completed

Domain Name, Student Name, Y
Domain Name, Student Name, N


Note the completed column is “Y” or “N”.

The problem is with my summary report. I’m grouping by domain name and splitting the detailed report column “Completed” of “Y” and “N” to the Summary columns of “Complete” and “Incomplete”. I changing the “Y” to 1 or 0, and the “N” to 1 or 0 and then SUM each column.

My detailed report returns 17k rows, my Summary report returns 174 rows but total of sums are not correct. Example output for summary report is one of three types: “Domain Name, 1, 0” or “Domain Name, 1,1” or “Domain Name, 0, 0 “. These rows should have numbers like “Domain Name, 254, 110”, etc.

Any help or guidance with code would be appreciated.

SELECT inner_clause.dmn_id,
SUM(decode(status_remday,'Y',1,0)) COMPLETED,
SUM(decode(status_remday,'N',1,0)) INCOMPLETED
FROM
(SELECT DISTINCT q.qual_id,
q.qual_title,
s.dmn_id,
SUBSTR(pkg_student.get_delm_stud_qual_stat_rmday(sq.stud_id, sq.qual_id, sq.qual_id),1,1) AS status_remday
FROM pa_stud_qual sq,
pa_student s,
pa_user_preference userPref,
pa_qual q
WHERE sq.stud_id = s.stud_id
AND s.stud_id = userPref.user_id(+)
AND userPref.user_type(+)='S'
AND sq.qual_id = q.qual_id
/** and q.qual_id in [CurriculumSearch] */
/** and s.stud_id in [UserSearch] */
/** and s.notactive = [UserStatus] */
/** and [security:pa_student s] */
) inner_clause
GROUP BY inner_clause.dmn_id
ORDER BY inner_clause.dmn_id

Answer

The inner clause needed to DISTINCT more fields for the result. The final solution was:

SELECT inner_clause.dmn_id,
       inner_clause.qual_title,
       SUM(decode(status_remday,'Y',1,0)) AS COMPLET,
       SUM(decode(status_remday,'N',1,0)) AS INCOMPLET,
       COUNT(decode(status_remday,'Y',1,'N',1)) AS TOTAL,
       SYSDATE AS CURRENTDATETIME
       FROM
       (SELECT DISTINCT q.qual_id,
             q.qual_title,
             sq.stud_id,
             s.lname,
             s.fname,
             s.mi,
             s.dmn_id,
             sq.assgn_dte,
             sq.qual_id,
             NVL(userPref.preferred_timezone,pkg_state.get_default_timezone()) AS preferred_timezone,
             DECODE(S.NOTACTIVE,'Y','label.NotActive', 'N','label.Active') AS NOTACTIVE,
             s.email_addr,
             SUBSTR(pkg_student.get_delm_stud_qual_stat_rmday(sq.stud_id, sq.qual_id, sq.qual_id),1,1) AS status_remday
             FROM pa_stud_qual sq,
                    pa_student s,
                    pa_user_preference userPref,
                    pa_qual q
             WHERE sq.stud_id = s.stud_id
             AND s.stud_id = userPref.user_id(+)
             AND userPref.user_type(+) = 'S'
             AND sq.qual_id = q.qual_id
             /** and q.qual_id in [CurriculumSearch] */
             /** and s.stud_id in [UserSearch] */
             /** and s.notactive = [UserStatus] */
             /** and [security:pa_student s] */
             ORDER BY 1
       ) inner_clause     
GROUP BY inner_clause.dmn_id, inner_clause.qual_title
ORDER BY inner_clause.dmn_id