Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Scott Gerhardt <scott@g-it.ca>
Subject: Re: Aggregate query for multiple records
Date: Fri, 27 Aug 2004 08:43:32 -0700
User-Agent: KMail/1.4.3
References: <641FEEDD-F728-11D8-A763-000393801C60@g-it.ca>
	<200408262155.42636.josh@agliodbs.com>
	<187ADA81-F7F3-11D8-A763-000393801C60@g-it.ca>
In-Reply-To: <187ADA81-F7F3-11D8-A763-000393801C60@g-it.ca>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200408270843.32059.josh@agliodbs.com>

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