Oracle Apex Bar Chart with Days of Week Aggregate

Question

I'm trying to create a bar chart (with Oracle Apex v21.1.0) showing employee absences broken down by days of the week. i.e. a count of absences for each day of the week. I'm having problems getting the 7 bars for the days of the week to show in chronological order. This is my SQL:

-- sql for chart showing count of days absent for each day of week
select 
  TO_CHAR(ABD_DATE, 'D') as Day_Index_DOW, 
  TO_CHAR(ABD_DATE, 'DY') as Day_Name_DOW, 
  COUNT(TO_CHAR(ABD_DATE, 'D')) as Count_DOW  
from F_ABSENT_DAYS
  where ABD_EMP_ID = :P410_EMP_ID
group by TO_CHAR(ABD_DATE, 'D'), TO_CHAR(ABD_DATE, 'DY')
order by TO_CHAR(ABD_DATE, 'D')

Chart settings are:

Series Name is set to Day_Index_DOW
Label is set to Day_Name_DOW
Value is set to Count_DOW

This produces the 7 bars with the correct counts for the days of week. The problem is, they are sorted in alphabetical order (FRI, MON, SAT...). I'm trying to get them in chronological order (MON, TUE, WED...).

Is the error in the SQL or a setting in the designer?

Mónica Godoy Mónica Godoy · Accepted Answer · 2021-06-24T22:13:14

I created sample data to demo your example. SQL Query:

select day_number, day_name, sum(count_dow)
from (
    select TO_CHAR(sysdate, 'D') day_number, TO_CHAR(sysdate, 'DY') day_name, 5 count_dow
    from dual
    union
    select TO_CHAR(sysdate+1, 'D') day_number, TO_CHAR(sysdate+1, 'DY') day_name, 4 count_dow
    from dual
    union
    select TO_CHAR(sysdate+2, 'D') day_number, TO_CHAR(sysdate+2, 'DY')day_name, 3 count_dow
    from dual
    union
    select TO_CHAR(sysdate+3, 'D') day_number, TO_CHAR(sysdate+3, 'DY')day_name, 2 count_dow
    from dual
    union
    select TO_CHAR(sysdate+4, 'D') day_number, TO_CHAR(sysdate+4, 'DY')day_name, 1 count_dow
    from dual
    union
    select TO_CHAR(sysdate+5, 'D') day_number, TO_CHAR(sysdate+5, 'DY')day_name, 4 count_dow
    from dual
    union
    select TO_CHAR(sysdate+6, 'D') day_number, TO_CHAR(sysdate+6, 'DY')day_name, 8 count_dow
    from dual
    union
    select TO_CHAR(sysdate+3, 'D') day_number, TO_CHAR(sysdate+3, 'DY')day_name, 2 count_dow
    from dual
    union
    select TO_CHAR(sysdate+4, 'D') day_number, TO_CHAR(sysdate+4, 'DY')day_name, 1 count_dow
    from dual
    union
    select TO_CHAR(sysdate+5, 'D') day_number, TO_CHAR(sysdate+5, 'DY')day_name, 4 count_dow
    from dual
    union
    select TO_CHAR(sysdate+6, 'D') day_number, TO_CHAR(sysdate+6, 'DY')day_name, 8 count_dow
    from dual
)
group by day_number, day_name
order by day_number;

I set the label and value properties:

This is the resulting chart:

According to NLS_TERRITORY parameter for my DB, the first day of the week is Sunday, can vary on yours.

Oracle Apex Bar Chart with Days of Week Aggregate

1 Answers