UNION opration NOT return all result set :) pls help - Mailing list pgsql-hackers
From | korapat |
---|---|
Subject | UNION opration NOT return all result set :) pls help |
Date | |
Msg-id | 35EAEE39.A5A58077@cdg.co.th Whole thread Raw |
List | 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: