Re: Aggregate query for multiple records

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