Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.2/8.9.1) with ESMTP id RAA47582; Tue, 9 Feb 1999 17:49:12 -0500 (EST) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id QAA19278; Tue, 9 Feb 1999 16:49:10 -0600 (CST) Received: from cpsmail.cpsgroup.com(144.210.12.11) by gauntlet.bjke.com via smap (4.1) id xmaa18027; Tue, 9 Feb 99 16:48:08 -0600 Received: by cpsmail with Internet Mail Service (5.0.1458.49) id ; Tue, 9 Feb 1999 16:50:48 -0600 Message-ID: From: "Jackson, DeJuan" To: Ludovico Magnocavallo , "Jackson, DeJuan" , pgsql-general@postgreSQL.org Cc: PGSQL SQL Subject: RE: [GENERAL] pqReadData() -- backend closed the channel unexpect edly. Date: Tue, 9 Feb 1999 16:50:46 -0600 X-Priority: 3 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.0.1458.49) Content-Type: text/plain You are correct... My query assumes uniqueness in o.origine and d.destinazione which is obviously incorrect. try these two queries, and let me know the results: INSERT INTO week (address, origbytes, when) SELECT o.origine, sum(o.bytes), '09/02/1999 00:59:59'::datetime FROM day o WHERE o.origine << '194.74.133.0/24' AND o.when BETWEEN '09/02/1999 00:00:01' AND '09/02/1999 00:59:59' GROUP BY o.origine,3; UPDATE week SET destbytes = sum(d.bytes) FROM day d WHERE d.destinazione=week.address AND week.when = '09/02/1999 00:59:59' AND d.when BETWEEN '09/02/1999 00:00:01' AND '09/02/1999 00:59:59' GROUP BY d.destinazione; Have you figured out the problem with the problem you were having with the date around 12:00? Let me know, DEJ > -----Original Message----- > Thanks again, Jackson, but your query is not right :( > Here is a sample output: > > your query: > > SELECT o.origine, sum(o.bytes), sum(d.bytes), '09/02/1999 > 00:59:59'::datetime > FROM day o, day d > where o.origine=d.destinazione > and o.origine << '194.74.133.0/24' > and o.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59' > and d.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59' > GROUP BY o.origine,4; > > result (only for host 194.74.133.11, query is too long): > > 194.74.133.11|18569856|13493249|09/02/1999 00:59:59.00 MET > > simple query to check first sum: > > SELECT o.origine, sum(o.bytes) > FROM day o > where o.origine = '194.74.133.11' > and o.when between '09/02/1999 00:00:01' and '09/02/1999 00:59:59' > GROUP BY o.origine; > > 194.74.133.11|250944 > > Huge difference! Moreover, your query is slow, much much > slower than two > separate queries. I would have used it though, if it worked. > Postgresql > keeps crashing on my loop. :( > > Thanks > > L. > >