From: Josh Berkus <josh@agliodbs.com>
To: Scott Gerhardt <scott@g-it.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: Aggregate query for multiple records
Date: Fri, 27 Aug 2004 08:43:32 -0700
Message-ID: <200408270843.32059.josh@agliodbs.com> (raw)
In-Reply-To: <187ADA81-F7F3-11D8-A763-000393801C60@g-it.ca>
References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
<200408262155.42636.josh@agliodbs.com>
<187ADA81-F7F3-11D8-A763-000393801C60@g-it.ca>
Scott,
> > SELECT wid,
> > (SELECT SUM(oil) FROM prd_data pd2
> > WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as tot_oil,
> > (SELECT SUM(hours) FROM prd_data pd2
> > WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as tot_hours
> > FROM prd_data
> > ORDER BY wid;
>
> There is only one table being queried (prd_data). What is the purpose
> of "pd2"?
It's a table alias. There may be only one table, but to do this in a single
query you need to reference 3 "instances" of that same table.
Hmmm ... let me make the distinction more clear:
SELECT wid,
(SELECT SUM(oil) FROM prd_data prd2
WHERE prd2.wid = prd1.wid ORDER BY "date" LIMIT 6) as tot_oil,
(SELECT SUM(hours) FROM prd3
WHERE prd3.wid = prd1.wid ORDER BY "date" LIMIT 6) as tot_hours
FROM prd_data prd1
ORDER BY wid;
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, scott@g-it.ca
Subject: Re: Aggregate query for multiple records
In-Reply-To: <200408270843.32059.josh@agliodbs.com>
* 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