Yuan Yao, An Oracle ACE's Blog

SELECT  
    TRIM(TO_CHAR(sample_time, 'DAY')) AS sample_day,
    SUM(CASE WHEN sql_id = 'ak9twwhh104uw' THEN 1 ELSE 0 END) AS id_ak9twwhh104uw,
    SUM(CASE WHEN sql_id = '8vmu6k690g87k' THEN 1 ELSE 0 END) AS id_8vmu6k690g87k,
    SUM(CASE WHEN sql_id = 'g7hczna7rus03' THEN 1 ELSE 0 END) AS id_g7hczna7rus03,
    SUM(CASE WHEN sql_id = '5987uswa9cjx4' THEN 1 ELSE 0 END) AS id_5987uswa9cjx4,
    SUM(CASE WHEN sql_id = '2zkxp8panuwxv' THEN 1 ELSE 0 END) AS id_2zkxp8panuwxv
FROM
    dba_hist_active_sess_history 
WHERE
    sample_time BETWEEN TRUNC(SYSDATE - 7) AND TRUNC(SYSDATE)
GROUP BY
    TO_CHAR(sample_time, 'DAY')
ORDER BY
    CASE TRIM(TO_CHAR(sample_time, 'DAY'))
        WHEN 'MONDAY' THEN 1
        WHEN 'TUESDAY' THEN 2
        WHEN 'WEDNESDAY' THEN 3
        WHEN 'THURSDAY' THEN 4
        WHEN 'FRIDAY' THEN 5
        WHEN 'SATURDAY' THEN 6
        WHEN 'SUNDAY' THEN 7
    END;

   SAMPLE_DAY    ID_AK9TWWHH104UW    ID_8VMU6K690G87K    ID_G7HCZNA7RUS03    ID_5987USWA9CJX4    ID_2ZKXP8PANUWXV
_____________ ___________________ ___________________ ___________________ ___________________ ___________________
MONDAY                       2187                2109                2897                2947                1538
TUESDAY                      1918                2162                2731                2518                1509
WEDNESDAY                    1118                 833                 997                   0                   0
THURSDAY                     3278                2886                   0                2268                1852
FRIDAY                       3556                3181                1010                2233                1804
SATURDAY                     2604                3018                2400                2483                2060
SUNDAY                       1953                2110                2981                   0                2020

7 rows selected.