WITH v1 ( toll_id, entry_plaza, exit_plaza, plate, toll_date ) 
     AS (SELECT 1, 
                101, 
                102, 
                'MARS01234', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 2, 
                101, 
                103, 
                'MARS9876', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 3, 
                101, 
                101, 
                'MARS7321', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 4, 
                101, 
                104, 
                '2', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 5, 
                101, 
                101, 
                'MARS2234', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 6, 
                101, 
                105, 
                'MARS7667', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual), 
     v2 ( violation_id, entry_plaza, exit_plaza, plate, toll_date ) 
     AS (SELECT 2, 
                101, 
                102, 
                'MARS01234', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 7, 
                101, 
                103, 
                'MARS8747', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 9, 
                101, 
                101, 
                'MARS9842', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 10, 
                101, 
                104, 
                'MARS01244', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 11, 
                101, 
                101, 
                '3', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 12, 
                101, 
                105, 
                'MARS09234', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 13, 
                101, 
                105, 
                'MARS09134', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual 
         UNION ALL 
         SELECT 14, 
                101, 
                105, 
                'MARS89234', 
                To_timestamp('21-Mar-17', 'dd-Mon-rr') 
         FROM   dual), 
     v3 (count_v1) 
     AS (SELECT Count(1) 
         FROM   v1), 
     v4 (count_v2) 
     AS (SELECT Count(1) 
         FROM   v2), 
     v ( toll_id, entry_plaza, exit_plaza, plate, toll_date ) 
     AS (SELECT toll_id, 
                entry_plaza, 
                exit_plaza, 
                plate, 
                toll_date 
         FROM   v1 
         WHERE  ( NOT( EXISTS(SELECT NULL 
                              FROM   v2 
                              WHERE  toll_id = v1.toll_id) ) ) 
         UNION ALL 
         SELECT violation_id, 
                entry_plaza, 
                exit_plaza, 
                plate, 
                toll_date 
         FROM   v2) -- select v3.count_v1 from v3  --6   
-- select v4.count_v2 from v4  -- 8   
SELECT To_char(Trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') AS date_hour, 
       Count(CASE 
               WHEN plate NOT IN ( '2', '3' ) THEN 1 
             END)                                             AS 
       count_plate_not_2_or_3, 
       Count(CASE 
               WHEN entry_plaza = 101 
                    AND exit_plaza = 101 THEN 1 
             END)                                             AS 
       count_101_entry_exit, 
       Round(Count(v4.count_v2) / Count(v3.count_v1), 1)      AS avg_trans_count 
FROM   v, 
       v3, 
       v4 
GROUP  BY Trunc(toll_date, 'HH') 
ORDER  BY 1; 

DATE_HOUR                        COUNT_PLATE_NOT_2_OR_3
--------------- ---------------------------------------
                   COUNT_101_ENTRY_EXIT                         AVG_TRANS_COUNT
--------------------------------------- ---------------------------------------
21-MAR-17 00 Hr                                       7 
                                      2                                       1