Creating report based on the sql statement

Question

I would like my report to display something image like as below

so can i know is it how my sql for table has to be when creating it

    create table attendance
    (
       Student_ID VARCHAR2 (15) primary key,
       Student_Name VARCHAR2 (50) not null,
       "Date1" VARCHAR2 (15) not null,
       "Date2" VARCHAR2 (15) not null,
       "Date3" VARCHAR2 (15) not null,
       "Date4" VARCHAR2 (15) not null,
       "Date5" VARCHAR2 (15) not null,
       Present number (10),
       Late number (10),
       Absent number (10),
       Total_Attendance number (10)
    );

for present, late, absent and total attendance i will be using like countif but this i have to research on it how to use in oracle. I would like to know is this correct the way it should be used for sql in creating table so that i will get a report the looks as in the picture

Answer 1

You can "translate" the appropriate text values to 1 or 0, then add them.

select
  student_id,
  student_name,
  date1,
  date2,
  date3,
  date4,
  date5,
  decode(date1, 'Present', 1, 0) +
  decode(date2, 'Present', 1, 0) +
  decode(date3, 'Present', 1, 0) +
  decode(date4, 'Present', 1, 0) +
  decode(date5, 'Present', 1, 0) as Present,
  decode(date1, 'Late', 1, 0) +
  decode(date2, 'Late', 1, 0) +
  decode(date3, 'Late', 1, 0) +
  decode(date4, 'Late', 1, 0) +
  decode(date5, 'Late', 1, 0) as Late,
  decode(date1, 'Absent', 1, 0) +
  decode(date2, 'Absent', 1, 0) +
  decode(date3, 'Absent', 1, 0) +
  decode(date4, 'Absent', 1, 0) +
  decode(date5, 'Absent', 1, 0) as Present,
  decode(date1, 'Present', 1, 'Late', 1, 0) +
  decode(date2, 'Present', 1, 'Late', 1, 0) +
  decode(date3, 'Present', 1, 'Late', 1, 0) +
  decode(date4, 'Present', 1, 'Late', 1, 0) +
  decode(date5, 'Present', 1, 'Late', 1, 0) as Total_attendance
from
  attendance;

CASE also works, but that is a bit longer:

...
case when date1 = 'Present' then 1 else 0 end +
case when date2 = 'Present' then 1 else 0 end +
case when date3 = 'Present' then 1 else 0 end +
case when date4 = 'Present' then 1 else 0 end +
case when date5 = 'Present' then 1 else 0 end as Present,
...