Home » SQL & PL/SQL » SQL & PL/SQL » SQL statement with averages
| SQL statement with averages [message #669339] |
Mon, 16 April 2018 10:42  |
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  |
 |
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 |
|
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 |
 |
quirks
Messages: 79
Registered: October 2014
|
Member |
|
|
You could go with an inline selectWITH
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; |
|
|
|
Goto Forum:
Current Time: Fri Jul 20 19:06:20 CDT 2018
|
] 
Members
Search
Help
Register
Login
Home
