2

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.

0

1 Answer 1

3

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
6
  • thanks, can i know like is there any reference i could search or what i should do in order to further investigate and gain knowledge i rhis. and is it possible to arrange in like the dates to appear first and then absent, late and present column after performing the pivot.? thanks a lot for the help
    – Jayam Koko
    Apr 4, 2016 at 15:38
  • @Jayam Koko can create new question about how to rearrange columns after pivoting. And dont forget to mark the answer as accepted answer once you got what you need :). For details SQL for Analysis and Reporting
    – atokpas
    Apr 4, 2016 at 15:55
  • 1
    @JayamKoko: You can certainly read the manual. You can try taking a look at similar questions here or on Stack Overflow as well. But of course reading alone is rarely enough, you need practising things too. In this particular case you've got a complex problem: not just pivoting but pivoting plus totals. So another time try analysing your problem to be able to determine when it's complex. That will let you work on each sub-problem separately – which usually (in my case – always) helps to learn things better.
    – Andriy M
    Apr 4, 2016 at 15:55
  • thanks so much, for all that help me, i appreciate it thanks for the advice too. I will learn so many things fro m u all.
    – Jayam Koko
    Apr 4, 2016 at 16:01
  • @JayamKoko: As for the column order, you could simply replace the * in your main select with an explicit column list in whatever order you need them. That doesn't seem to me worth asking a new question, but that's because I don't know of other ways and I'm not an Oracle expert (I'm a SQL Server person actually). Perhaps there are some other tricks and maybe that's what JSapkota had in mind when suggesting you post another question.
    – Andriy M
    Apr 4, 2016 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.