Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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


pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group