UNION opration NOT return all result set :) pls help

From: korapat <korapat(dot)c(at)cdg(dot)co(dot)th>
To: pgsql-hackers(at)postgresql(dot)org
Subject: UNION opration NOT return all result set :) pls help
Date: 1998-08-31 18:40:57
Message-ID: 35EAEE39.A5A58077@cdg.co.th
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Keith Parks 1998-08-31 19:19:29 Re: [HACKERS] Core dump in regression tests.
Previous Message Bruce Momjian 1998-08-31 18:06:48 Re: [HACKERS] flock patch breaks things here