pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Scott Gerhardt <scott@g-it.ca>
To: pgsql-sql@postgresql.org
Subject: Aggregate query for multiple records
Date: Thu, 26 Aug 2004 00:23:05 -0600
Message-ID: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca> (raw )
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
view thread (9+ messages) latest in thread
Message-ID: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
Permalink: ../641FEEDD-F728-11D8-A763-000393801C60@g-it.ca/
Also on: postgresql.org/message-id/641FEEDD-F728-11D8-A763-000393801C60@g-it.ca
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scott@g-it.ca
Subject: Re: Aggregate query for multiple records
In-Reply-To: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox