Home » SQL & PL/SQL » SQL & PL/SQL » SQL statement with averages
SQL statement with averages [message #669339] Mon, 16 April 2018 10:42 Go to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
Below are my Table (Insert/Structure) statements
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

Quote:

But, I would like the output 1.3 as value in the last column of above output as below:

-- select v4.count_v2 from v4 -- 8

-- select v3.count_v1 from v3 --6

select round(8/6,1) from dual -- 1.3

I have included create Table (Insert/Structure) statements
Re: SQL statement with averages [message #669607 is a reply to message #669339] Wed, 02 May 2018 13:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8856
Registered: November 2002
Location: California, USA
Senior Member
The value of COUNT(V3.COUNT_V1) is 8, not 6, therefore the result is 1, not 1.3, as demonstrated below.

SCOTT@orcl_12.1.0.2.0> WITH v1 ( toll_id, entry_plaza, exit_plaza, plate, toll_date )
  2  	  AS (SELECT 1,
  3  		     101,
  4  		     102,
  5  		     'MARS01234',
  6  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
  7  	      FROM   dual
  8  	      UNION ALL
  9  	      SELECT 2,
 10  		     101,
 11  		     103,
 12  		     'MARS9876',
 13  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 14  	      FROM   dual
 15  	      UNION ALL
 16  	      SELECT 3,
 17  		     101,
 18  		     101,
 19  		     'MARS7321',
 20  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 21  	      FROM   dual
 22  	      UNION ALL
 23  	      SELECT 4,
 24  		     101,
 25  		     104,
 26  		     '2',
 27  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 28  	      FROM   dual
 29  	      UNION ALL
 30  	      SELECT 5,
 31  		     101,
 32  		     101,
 33  		     'MARS2234',
 34  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 35  	      FROM   dual
 36  	      UNION ALL
 37  	      SELECT 6,
 38  		     101,
 39  		     105,
 40  		     'MARS7667',
 41  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 42  	      FROM   dual),
 43  	  v2 ( violation_id, entry_plaza, exit_plaza, plate, toll_date )
 44  	  AS (SELECT 2,
 45  		     101,
 46  		     102,
 47  		     'MARS01234',
 48  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 49  	      FROM   dual
 50  	      UNION ALL
 51  	      SELECT 7,
 52  		     101,
 53  		     103,
 54  		     'MARS8747',
 55  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 56  	      FROM   dual
 57  	      UNION ALL
 58  	      SELECT 9,
 59  		     101,
 60  		     101,
 61  		     'MARS9842',
 62  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 63  	      FROM   dual
 64  	      UNION ALL
 65  	      SELECT 10,
 66  		     101,
 67  		     104,
 68  		     'MARS01244',
 69  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 70  	      FROM   dual
 71  	      UNION ALL
 72  	      SELECT 11,
 73  		     101,
 74  		     101,
 75  		     '3',
 76  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 77  	      FROM   dual
 78  	      UNION ALL
 79  	      SELECT 12,
 80  		     101,
 81  		     105,
 82  		     'MARS09234',
 83  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 84  	      FROM   dual
 85  	      UNION ALL
 86  	      SELECT 13,
 87  		     101,
 88  		     105,
 89  		     'MARS09134',
 90  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 91  	      FROM   dual
 92  	      UNION ALL
 93  	      SELECT 14,
 94  		     101,
 95  		     105,
 96  		     'MARS89234',
 97  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 98  	      FROM   dual),
 99  	  v3 (count_v1)
100  	  AS (SELECT Count(1)
101  	      FROM   v1),
102  	  v4 (count_v2)
103  	  AS (SELECT Count(1)
104  	      FROM   v2),
105  	  v ( toll_id, entry_plaza, exit_plaza, plate, toll_date )
106  	  AS (SELECT toll_id,
107  		     entry_plaza,
108  		     exit_plaza,
109  		     plate,
110  		     toll_date
111  	      FROM   v1
112  	      WHERE  ( NOT( EXISTS(SELECT NULL
113  				   FROM   v2
114  				   WHERE  toll_id = v1.toll_id) ) )
115  	      UNION ALL
116  	      SELECT violation_id,
117  		     entry_plaza,
118  		     exit_plaza,
119  		     plate,
120  		     toll_date
121  	      FROM   v2)
122  SELECT To_char(Trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') AS date_hour,
123  	    Count(CASE
124  		    WHEN plate NOT IN ( '2', '3' ) THEN 1
125  		  END)						   AS
126  	    count_plate_not_2_or_3,
127  	    Count(CASE
128  		    WHEN entry_plaza = 101
129  			 AND exit_plaza = 101 THEN 1
130  		  END)						   AS
131  	    count_101_entry_exit,
132  	    Count(v4.count_v2),
133  	    Count(v3.count_v1),
134  	    Round(Count(v4.count_v2) / Count(v3.count_v1), 1)	   AS avg_trans_count
135  FROM   v,
136  	    v3,
137  	    v4
138  GROUP  BY Trunc(toll_date, 'HH')
139  ORDER  BY 1
140  /

DATE_HOUR                COUNT_PLATE_NOT_2_OR_3 COUNT_101_ENTRY_EXIT COUNT(V4.COUNT_V2) COUNT(V3.COUNT_V1) AVG_TRANS_COUNT
------------------------ ---------------------- -------------------- ------------------ ------------------ ---------------
21-MAR-17 00 Hr                               7                    2                  8                  8               1

1 row selected.
Re: SQL statement with averages [message #669609 is a reply to message #669339] Wed, 02 May 2018 13:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8856
Registered: November 2002
Location: California, USA
Senior Member
Perhaps this is what you want instead, using just Round(v4.count_v2 / v3.count_v1, 1) without the extra counts and adding them to the grouping.

SCOTT@orcl_12.1.0.2.0> WITH v1 ( toll_id, entry_plaza, exit_plaza, plate, toll_date )
  2  	  AS (SELECT 1,
  3  		     101,
  4  		     102,
  5  		     'MARS01234',
  6  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
  7  	      FROM   dual
  8  	      UNION ALL
  9  	      SELECT 2,
 10  		     101,
 11  		     103,
 12  		     'MARS9876',
 13  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 14  	      FROM   dual
 15  	      UNION ALL
 16  	      SELECT 3,
 17  		     101,
 18  		     101,
 19  		     'MARS7321',
 20  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 21  	      FROM   dual
 22  	      UNION ALL
 23  	      SELECT 4,
 24  		     101,
 25  		     104,
 26  		     '2',
 27  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 28  	      FROM   dual
 29  	      UNION ALL
 30  	      SELECT 5,
 31  		     101,
 32  		     101,
 33  		     'MARS2234',
 34  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 35  	      FROM   dual
 36  	      UNION ALL
 37  	      SELECT 6,
 38  		     101,
 39  		     105,
 40  		     'MARS7667',
 41  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 42  	      FROM   dual),
 43  	  v2 ( violation_id, entry_plaza, exit_plaza, plate, toll_date )
 44  	  AS (SELECT 2,
 45  		     101,
 46  		     102,
 47  		     'MARS01234',
 48  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 49  	      FROM   dual
 50  	      UNION ALL
 51  	      SELECT 7,
 52  		     101,
 53  		     103,
 54  		     'MARS8747',
 55  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 56  	      FROM   dual
 57  	      UNION ALL
 58  	      SELECT 9,
 59  		     101,
 60  		     101,
 61  		     'MARS9842',
 62  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 63  	      FROM   dual
 64  	      UNION ALL
 65  	      SELECT 10,
 66  		     101,
 67  		     104,
 68  		     'MARS01244',
 69  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 70  	      FROM   dual
 71  	      UNION ALL
 72  	      SELECT 11,
 73  		     101,
 74  		     101,
 75  		     '3',
 76  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 77  	      FROM   dual
 78  	      UNION ALL
 79  	      SELECT 12,
 80  		     101,
 81  		     105,
 82  		     'MARS09234',
 83  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 84  	      FROM   dual
 85  	      UNION ALL
 86  	      SELECT 13,
 87  		     101,
 88  		     105,
 89  		     'MARS09134',
 90  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 91  	      FROM   dual
 92  	      UNION ALL
 93  	      SELECT 14,
 94  		     101,
 95  		     105,
 96  		     'MARS89234',
 97  		     To_timestamp('21-Mar-17', 'dd-Mon-rr')
 98  	      FROM   dual),
 99  	  v3 (count_v1)
100  	  AS (SELECT Count(1)
101  	      FROM   v1),
102  	  v4 (count_v2)
103  	  AS (SELECT Count(1)
104  	      FROM   v2),
105  	  v ( toll_id, entry_plaza, exit_plaza, plate, toll_date )
106  	  AS (SELECT toll_id,
107  		     entry_plaza,
108  		     exit_plaza,
109  		     plate,
110  		     toll_date
111  	      FROM   v1
112  	      WHERE  ( NOT( EXISTS(SELECT NULL
113  				   FROM   v2
114  				   WHERE  toll_id = v1.toll_id) ) )
115  	      UNION ALL
116  	      SELECT violation_id,
117  		     entry_plaza,
118  		     exit_plaza,
119  		     plate,
120  		     toll_date
121  	      FROM   v2)
122  SELECT To_char(Trunc(toll_date, 'HH'), 'DD-MON-RR HH24 "Hr"') AS date_hour,
123  	    Count(CASE
124  		    WHEN plate NOT IN ( '2', '3' ) THEN 1
125  		  END)						   AS
126  	    count_plate_not_2_or_3,
127  	    Count(CASE
128  		    WHEN entry_plaza = 101
129  			 AND exit_plaza = 101 THEN 1
130  		  END)						   AS
131  	    count_101_entry_exit,
132  	    Round(v4.count_v2 / v3.count_v1, 1)      AS avg_trans_count
133  FROM   v,
134  	    v3,
135  	    v4
136  GROUP  BY Trunc(toll_date, 'HH'), v4.count_v2, v3.count_v1
137  ORDER  BY 1
138  /

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

1 row selected.
Re: SQL statement with averages [message #669619 is a reply to message #669609] Thu, 03 May 2018 02:19 Go to previous message
quirks
Messages: 79
Registered: October 2014
Member
You could go with an inline select
WITH
    V1(TOLL_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE) AS
        (SELECT 1, 101, 102, 'MARS01234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 2, 101, 103, 'MARS9876', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 3, 101, 101, 'MARS7321', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 4, 101, 104, '2', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 5, 101, 101, 'MARS2234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 6, 101, 105, 'MARS7667', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL),
    V2(VIOLATION_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE) AS
        (SELECT 2, 101, 102, 'MARS01234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 7, 101, 103, 'MARS8747', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 9, 101, 101, 'MARS9842', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 10, 101, 104, 'MARS01244', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 11, 101, 101, '3', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 12, 101, 105, 'MARS09234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 13, 101, 105, 'MARS09134', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 14, 101, 105, 'MARS89234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') 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 'Since TOLL_ID = V1.TOLL_ID this will always EXIST!' -- What's that?
                             FROM V2
                            WHERE TOLL_ID = V1.TOLL_ID)))
         UNION ALL
         SELECT VIOLATION_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE FROM V2)
SELECT   TO_CHAR(TRUNC(TOLL_DATE, 'HH'), 'DD.MM.RRRR 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((SELECT V4.COUNT_V2 / V3.COUNT_V1 FROM V3, V4),1) -- Try this
             AS AVG_TRANS_COUNT
    FROM V
GROUP BY TRUNC(TOLL_DATE, 'HH')
ORDER BY 1;

But what bothers me is what you do in "V". Since you will never get Results from V1 you can just select everything from V2 in your last statement:
WITH
    V1(TOLL_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE) AS
        (SELECT 1, 101, 102, 'MARS01234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 2, 101, 103, 'MARS9876', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 3, 101, 101, 'MARS7321', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 4, 101, 104, '2', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 5, 101, 101, 'MARS2234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 6, 101, 105, 'MARS7667', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL),
    V2(VIOLATION_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE) AS
        (SELECT 2, 101, 102, 'MARS01234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 7, 101, 103, 'MARS8747', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 9, 101, 101, 'MARS9842', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 10, 101, 104, 'MARS01244', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 11, 101, 101, '3', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 12, 101, 105, 'MARS09234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 13, 101, 105, 'MARS09134', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 14, 101, 105, 'MARS89234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL),
    V3(COUNT_V1) AS (SELECT COUNT(1) FROM V1),
    V4(COUNT_V2) AS (SELECT COUNT(1) FROM V2)
SELECT   TO_CHAR(TRUNC(TOLL_DATE, 'HH'), 'DD.MM.RRRR 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((SELECT V4.COUNT_V2 / V3.COUNT_V1 FROM V3, V4),1)
             AS AVG_TRANS_COUNT
    FROM V2 -- just get rid of V
GROUP BY TRUNC(TOLL_DATE, 'HH')
ORDER BY 1;

I assume that you might want to exclude the rows from V1 which have a match in V2 but the matching criteria is not clear. If it's V2.VIOLATION_ID = V1.TOLL_ID this might help:
WITH
    V1(TOLL_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE) AS
        (SELECT 1, 101, 102, 'MARS01234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 2, 101, 103, 'MARS9876', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 3, 101, 101, 'MARS7321', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 4, 101, 104, '2', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 5, 101, 101, 'MARS2234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 6, 101, 105, 'MARS7667', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL),
    V2(VIOLATION_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE) AS
        (SELECT 2, 101, 102, 'MARS01234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 7, 101, 103, 'MARS8747', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 9, 101, 101, 'MARS9842', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 10, 101, 104, 'MARS01244', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 11, 101, 101, '3', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 12, 101, 105, 'MARS09234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 13, 101, 105, 'MARS09134', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') FROM DUAL
         UNION ALL
         SELECT 14, 101, 105, 'MARS89234', TO_TIMESTAMP('21.04.2017', 'DD.MM.RRRR') 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 'Here you filter out every row from V1 where V2.VIOLATION_ID = V1.TOLL_ID'
                             FROM V2
                            WHERE V2.VIOLATION_ID = V1.TOLL_ID)))  -- probably you need a different matching / filter criteria
         UNION ALL
         SELECT VIOLATION_ID, ENTRY_PLAZA, EXIT_PLAZA, PLATE, TOLL_DATE FROM V2)
SELECT   TO_CHAR(TRUNC(TOLL_DATE, 'HH'), 'DD.MM.RRRR 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((SELECT V4.COUNT_V2 / V3.COUNT_V1 FROM V3, V4) ,1)
             AS AVG_TRANS_COUNT
    FROM V
GROUP BY TRUNC(TOLL_DATE, 'HH')
ORDER BY 1;
Previous Topic: Stored procedure to load initial bulk load from one DB to another DB
Next Topic: Format a String with Decimal Point using REGEXP_REPLACE
Goto Forum:
  


Current Time: Fri Jul 20 19:06:20 CDT 2018