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.