Aggregate query for multiple records - Mailing list pgsql-sql

From Scott Gerhardt
Subject Aggregate query for multiple records
Date
Msg-id 641FEEDD-F728-11D8-A763-000393801C60@g-it.ca
Whole thread Raw
Responses Re: Aggregate query for multiple records
List pgsql-sql
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



pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: ERROR: Invalid regular expression: parentheses ( ) not balanced
Next
From: "Riccardo G. Facchini"
Date:
Subject: from PG_DUMP to CVS