Problem using pivot to display data

Question

I currently have a table called sample, the structure of my attendance table is:

create table sample
(
  Student_ID VARCHAR2 (10),
  Late_Attendance number (10) Default 3,
  Attendance_Date Date,
  Attendance_ID VARCHAR2 (10)
);

Sample data:

insert into sample 
values ('SCM-026020', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'LATE');
insert into sample 
values ('SCM-026021', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample 
values ('SCM-026022', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'PRESENT');
insert into sample 
values ('SCM-026020', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample 
values ('SCM-026021', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample 
values ('SCM-026022', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'PRESENT');

This is the SQL I used based on pivot:

select * 
from  (
        select student_id, attendance_Date, attendance_id, 
        sum (DECODE (Attendance_id, 'ABSENT', 1, 0)) absent,
        SUM (DECODE (Attendance_ID, 'LATE', 1, 0)) LATE,
        SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) PRESENT
        from sample
        group by student_id, attendance_id, attendance_date
      )

pivot (
          MAX (Attendance_ID)
          for Attendance_Date IN (To_Date ('4/4/2016', 'DD/MM/YYYY') "4/4/2016", 
                                  To_Date ('5/4/2016', 'DD/MM/YYYY') "5/4/2016") 
      )

Why I am getting such weird output?

STUDENT_ID     ABSENT       LATE    PRESENT 4/4/2016   5/4/2016 
---------- ---------- ---------- ---------- ---------- ----------
SCM-026021          1          0          0 ABSENT     ABSENT    
SCM-026022          0          0          1 PRESENT    PRESENT   
SCM-026020          1          0          0            ABSENT    
SCM-026020          0          1          0 LATE                 

The Student_ID scm-026020 is appearing twice which should only appear once and the sum such as absent for scm-026021 should be 2 but displaying as 1.

My expected output would be:

STUDENT_ID     ABSENT       LATE    PRESENT 4/4/2016   5/4/2016 
---------- ---------- ---------- ---------- ---------- ----------
SCM-026021          2          0          0 ABSENT     ABSENT    
SCM-026022          0          0          2 PRESENT    PRESENT   
SCM-026020          1          1          0 LATE       ABSENT                    

There should always be one row per student and day.

If I remove the sum (DECODE (Attendance_id, 'ABSENT', 1, 0)) absent part, it gives the correct output, but I need to put this since I need to calculate at the end the number of attendance days they are late, absent, and present.

Answer 1

The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details.

The source in your query is not providing that kind of information. It is grouping by student_id, attendance_Date, attendance_id, and that grouping does not have any effect since there is always one row per student and date.

In order to have both details and totals on the same row, you can use window aggregation, like this:

SELECT
  student_id,
  attendance_Date,
  attendance_id, 
  SUM (DECODE (Attendance_id, 'ABSENT',  1, 0)) OVER (PARTITION BY student_id) AS ABSENT,
  SUM (DECODE (Attendance_ID, 'LATE',    1, 0)) OVER (PARTITION BY student_id) AS LATE,
  SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) OVER (PARTITION BY student_id) AS PRESENT
FROM
  sample

Note absence of GROUP BY. The OVER clause makes SUM a window aggregate function. With a window function you can return aggregate data along with detail (non-aggregate) data. The query above returns output like this:

STUDENT_ID  ATTENDANCE_DATE  ATTENDANCE_ID  ABSENT  LATE  PRESENT
----------  ---------------  -------------  ------  ----  -------
SCM-026020  4/4/2016         LATE           1       1     0
SCM-026020  5/4/2016         ABSENT         1       1     0
SCM-026021  5/4/2016         ABSENT         2       0     0
SCM-026021  4/4/2016         ABSENT         2       0     0
SCM-026022  4/4/2016         PRESENT        0       0     2
SCM-026022  5/4/2016         PRESENT        0       0     2

That is, it returns details per date and student along with totals per student. Using that source, the PIVOT clause provides the expected result:

STUDENT_ID  ABSENT  LATE  PRESENT  4/4/2016  5/4/2016
----------  ------  ----  -------  --------  --------
SCM-026020  1       1     0        LATE      ABSENT
SCM-026021  2       0     0        ABSENT    ABSENT
SCM-026022  0       0     2        PRESENT   PRESENT