I'm trying to get a table which shows monthly attendance details for students.

What I have is a pivot which sums up the number of absences based on the student's course load.

Say for instance I knew this student has 4 classes, all the time, regardless of school year or semester, this would work perfectly:

```
SELECT * FROM
(SELECT SCHOOL_YEAR, TO_CHAR(CALENDAR_DATE, 'mon') MNTH, ATTENDANCE_CODE
FROM PERIOD_ATTENDANCE
WHERE PERSON_ID = '1234'
AND SCHOOL_CODE IN ('ESS', 'ASS', 'BSS')
)
PIVOT (SUM(CASE WHEN ATTENDANCE_CODE = 'L' THEN 1 END) as L,
SUM(CASE WHEN ATTENDANCE_CODE = 'A'
AND MNTH IN ('sep', 'oct', 'nov', 'dec', 'jan')
THEN 1/4
WHEN ATTENDANCE_CODE = 'A'
AND MNTH IN ('feb', 'mar', 'apr', 'may', 'jun')
THEN 1/4 END) as A
FOR MNTH
IN (
'sep',-- AS SEPTEMBRE,
'oct',-- AS OCTOBRE,
'nov',-- AS NOVEMBRE,
'dec',-- AS DECEMBRE,
'jan', -- AS JANVIER,
'feb',-- AS FÉVRIER,
'mar',-- AS MARS,
'apr',-- AS AVRIL,
'may',-- AS MAI,
'jun'-- AS JUIN
) -- END <IN>
) -- END PIVOT
```

But since some students take fewer, or more than the average 4 courses per semester, I need to find a way to make that "4" (in "`THEN 1/4 END`

") represent the actual number of courses the student is taking.

I came up with a function which returns this number, but as soon as I plug it into the pivot the query only returns a single row, which displays a sum of all the student's absences from each year.

```
PIVOT (SUM(CASE WHEN ATTENDANCE_CODE = 'L' THEN 1 END) as L,
SUM(CASE WHEN ATTENDANCE_CODE = 'A'
AND MNTH IN ('sep', 'oct', 'nov', 'dec', 'jan')
THEN 1/PKG_PROFILE_ÉLÈVE.GET_COURSE_COUNT(SCHOOL_YEAR, '123456789', 1)
WHEN ATTENDANCE_CODE = 'A'
AND MNTH IN ('feb', 'mar', 'apr', 'may', 'jun')
THEN 1/PKG_PROFILE_ÉLÈVE.GET_COURSE_COUNT(SCHOOL_YEAR, '123456789', 2) END) as A
```

It returns the following, and you'll notice the `school_year`

column has vanished as well.

```
'sep'_L 'sep'_A 'oct'_L 'oct'_A 'nov'_L 'nov'_A 'dec'_L 'dec'_A 'jan'_L 'jan'_A 'feb'_L 'feb'_A 'mar'_L 'mar'_A 'apr'_L 'apr'_A 'may'_L 'may'_A 'jun'_L 'jun'_A
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2 0.25 2 0.75 2 0.25 1 1.75 2 1 7 0.75 1 0.25 1 0.25 1 0.25
```

This happens regardless of what's actually in the function. The function can simple be the following and it will still return a single aggregaded row.

```
FUNCTION GET_COURSE_COUNT(A_SCHOOL_YEAR VARCHAR2, A_PERSON_ID VARCHAR2, A_SEMESTER VARCHAR2) RETURN NUMBER IS
NUM_COURSE NUMBER := NULL;
BEGIN
NUM_COURSE = 4;
RETURN NUM_COURSE;
END GET_COURSE_COUNT;
```

Does anyone know if this is caused because of the PIVOT function itself? Does the function somehow force the aggregation and sum of the rows?

I've been looking around for two days now, and I can't seem to find a reason something like this would happen. It must be something obvious that I'm missing...

The actual function looks like this:

```
FUNCTION GET_COURSE_COUNT(A_SCHOOL_YEAR VARCHAR2, A_PERSON_ID VARCHAR2, A_SEMESTER VARCHAR2) RETURN NUMBER IS
NUM_COURSE NUMBER := NULL;
BEGIN
SELECT
COUNT( CASE --total des cours prit durant le semestre spécifié
WHEN SEMESTER = A_SEMESTER
THEN SEMESTER END) AS SEMESTRE INTO NUM_COURSE
FROM (SELECT DISTINCT SCT.SCHOOL_YEAR,
SCT.COURSE_CODE || '-' || SCT.COURSE_SECTION AS COURSE,
SC.TAKE_ATTENDANCE_FLAG, CM.SEMESTER
FROM SCHOOL_CLASSES SC, STUDENT_PROGRAM_CLASS_TRACKS SCT, CLASS_MEETINGS CM
WHERE SCT.PERSON_ID = A_PERSON_ID
AND SCT.SCHOOL_CODE IN ('ESS', 'ASS', 'BSS')
AND SC.SCHOOL_CODE = SCT.SCHOOL_CODE
AND SCT.SCHOOL_YEAR = A_SCHOOL_YEAR
AND SC.SCHOOL_YEAR = SCT.SCHOOL_YEAR
AND SC.CLASS_CODE = SCT.CLASS_CODE
AND SCT.SCHOOL_CODE = CM.SCHOOL_CODE
AND SCT.CLASS_CODE = CM.CLASS_CODE
AND CM.SCHOOL_YEAR = SCT.SCHOOL_YEAR
AND SCT.SCHOOL_YEAR_TRACK = CM.SCHOOL_YEAR_TRACK
AND SCT.DEMIT_INDICATOR NOT IN ('9')
ORDER BY SCT.SCHOOL_YEAR, CM.SEMESTER, COURSE);
RETURN NUM_COURSE;
END GET_COURSE_COUNT;
```