X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 391AB5E40AC for ; Thu, 26 Aug 2004 03:23:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14872-01 for ; Thu, 26 Aug 2004 06:23:08 +0000 (GMT) Received: from blue.gerhardt-it.com (gw.gerhardt-it.com [204.83.38.103]) by svr1.postgresql.org (Postfix) with ESMTP id 21F165E37D0 for ; Thu, 26 Aug 2004 03:23:06 -0300 (ADT) Received: from [70.64.67.67] (S0106000393801c60.ss.shawcable.net [70.64.67.67]) by blue.gerhardt-it.com (Postfix) with ESMTP id 33319FDC0 for ; Thu, 26 Aug 2004 00:23:06 -0600 (CST) Mime-Version: 1.0 (Apple Message framework v619) Content-Transfer-Encoding: 7bit Message-Id: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca> Content-Type: text/plain; charset=US-ASCII; format=flowed To: pgsql-sql@postgresql.org From: Scott Gerhardt Subject: Aggregate query for multiple records Date: Thu, 26 Aug 2004 00:23:05 -0600 X-Mailer: Apple Mail (2.619) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/280 X-Sequence-Number: 18777 Hello, I am new to the list, my apology if this question is beyond the scope or charter of this list. My questions is: What is the best method to perform an aggregate query to calculate sum() values for each distinct wid as in the example below, but except for all wid's (not just WHERE wid='01/1-6-1-30w1/0'). Also, performance wise, would it be better to build a function for this query. The table has 9 million records and these aggregate queries take hours. SELECT SUM(oil) as sumoil, SUM(hours) as sumhours, FROM (SELECT oil, hours prd_data WHERE wid='01/1-6-1-30w1/0' ORDER BY date LIMIT 6) subtable ; Table description: Table "prd_data" Column | Type | Modifiers --------+-----------------------+----------- date | integer | hours | real | oil | real | gas | real | water | real | pwid | integer | wid | character varying(20) | year | smallint | Indexes: wid_index6 Actual table (prd_data), 9 million records: date | hours | oil | gas | water | pwid | wid | year --------+-------+-------+------+-------+------+-----------------+------ 196507 | 360 | 159.4 | 11.3 | 40.9 | 413 | 01/1-1-1-31w1/0 | 1965 196508 | 744 | 280 | 20 | 27.2 | 413 | 01/1-1-1-31w1/0 | 1965 196509 | 360 | 171.1 | 11.4 | 50.4 | 413 | 01/1-1-1-31w1/0 | 1965 196510 | 744 | 202.1 | 25 | 89.8 | 413 | 01/1-1-1-31w1/0 | 1965 196512 | 744 | 201.3 | 23.8 | 71.9 | 413 | 01/1-1-1-31w1/0 | 1965 196511 | 720 | 184 | 17.6 | 78.9 | 413 | 01/1-1-1-31w1/0 | 1965 196610 | 744 | 99.8 | 15.4 | 53.7 | 413 | 01/1-1-1-31w1/0 | 1966 196612 | 744 | 86 | 12.8 | 36.1 | 413 | 01/1-1-1-31w1/0 | 1966 196611 | 720 | 86 | 12.6 | 41.7 | 413 | 01/1-1-1-31w1/0 | 1966 196601 | 744 | 191.6 | 22.6 | 50.7 | 413 | 01/1-1-1-31w1/0 | 1966 200301 | 461 | 68.8 | 0 | 186.3 | 47899 | 9G/6-1-50-24w3/0 | 2003 200310 | 740 | 446.3 | 0 | 563.1 | 47899 | 9G/6-1-50-24w3/0 | 2003 200306 | 667 | 92.1 | 0 | 968.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 200304 | 0 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003 200308 | 457 | 100.7 | 0 | 82.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 200307 | 574 | 78 | 0 | 752 | 47899 | 9G/6-1-50-24w3/0 | 2003 200312 | 582 | 360.9 | 0 | 569 | 47899 | 9G/6-1-50-24w3/0 | 2003 200311 | 681 | 260.8 | 0 | 563.9 | 47899 | 9G/6-1-50-24w3/0 | 2003 200305 | 452 | 0 | 0 | 0 | 47899 | 9G/6-1-50-24w3/0 | 2003 200309 | 637 | 244.6 | 0 | 193.8 | 47899 | 9G/6-1-50-24w3/0 | 2003 (20 rows) Thanks, -- Scott A. Gerhardt, P.Geo. Gerhardt Information Technologies