Dear Sir,
I've used UNION operator to combine 3 queries.
But I found that
- the number of tuples return from UNION operation was LESS than
the sum of each query tuples
- UNION or UNION ALL return the same result set
PLEASE SUGGEST ME WHERE IS INCORRECT. @^.^@
& HOW TO SOLVE IT.
These are my SQL statements & Tables:-
Union Statement
SELECT 21, date, service, sum(packets),
sum(bytes)
FORM t,ip_in
WHERE time like '21:%' and t.dst = ip_in.ip
GROUP BY date,service
union all
SELECT 22, date, service, sum(packets),
sum(bytes)
FORM t,ip_in
WHERE time like '22:%' and t.dst = ip_in.ip
GROUP BY date,service
union all
SELECT 23, date, service, sum(packets),
sum(bytes)
FORM t,ip_in
WHERE time like '23:%' and t.dst = ip_in.ip
GROUP BY date,service
Results Are :-
?column?|date |service |sum| sum
--------+-----------+-----------+---+------
21|1Jun98 |http |683|451852
22|1Jun98 |nbdatagram | 2| 477
22|1Jun98 |nbname | 32| 4096
21|1Jun98 |proxy-squid|240| 86151
21|1Jun98 |smtp | 41| 5063
(5 rows)
???????????? But Each Query return More Tuples
SELECT 21, date, service, sum(packets),
sum(bytes)
FORM t,ip_in
WHERE time like '21:%' and t.dst = ip_in.ip
GROUP BY date,service;
Results Are :-
?column?|date |service |sum| sum
--------+-----------+-----------+---+------
21|1Jun98 |http |683|451852
21|1Jun98 |proxy-squid|240| 86151
21|1Jun98 |smtp | 41| 5063
(3 rows)
SELECT 22, date, service, sum(packets),
sum(bytes)
FORM t,ip_in
WHERE time like '22:%' and t.dst = ip_in.ip
GROUP BY date,service;
Results Are :-
?column?|date |service | sum| sum
--------+-----------+-----------+----+------
22|1Jun98 |http |2644|942623
22|1Jun98 |nbdatagram | 2| 477
22|1Jun98 |nbname | 32| 4096
22|1Jun98 |proxy-squid| 99| 28356
(4 rows)
SELECT 23, date, service, sum(packets),
sum(bytes)
FORM t,ip_in
WHERE time like '23:%' and t.dst = ip_in.ip
GROUP BY date,service;
Results Are :-
?column?|date |service |sum| sum
--------+-----------+-----------+---+------
23|1Jun98 |http |714|189209
23|1Jun98 |proxy-squid|964|337385
(2 rows)
I used 2 table ;
-one had only 1 attribute ( contain no duplicate value
and is contained
in join condition , t.dst = ip_in.ip)
-and the other had 7.
Table = ip_in
+----------------------------------+----------------------------------+-------+
| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+
| ip | varchar()
| 50 |
+----------------------------------+----------------------------------+-------+
Table = t
+----------------------------------+----------------------------------+-------+
| Field |
Type | Length|
+----------------------------------+----------------------------------+-------+
| num |
varchar() | 20 |
| date |
char() | 11 |
| time |
char() | 8 |
| dst |
varchar() | 50 |
| service | varchar()
| 15 |
| packets | int4
| 4 |
| bytes | int4
| 4 |
+----------------------------------+----------------------------------+-------+
THANK YOU VERY MUCH,
Charu. Korapat