Re: Aggregate query for multiple records

Scott,

> Hello, I am new to the list, my apology if this question is beyond the
> scope or charter of this list.

We have a charter?   Why didn't anyone tell me?   

> 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').

> 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

Your problem here is the "limit 6"  There isn't any good+fast way to take only 
6 of each thing and total them.   Also the above query is missing a FROM 
clause, so I;ve had to guess at your table name below.  Oh, and the word 
"date" is a reserved word, better to quote it. 

This is valid, but it won't be fast:

SELECT wid, (SELECT SUM(oil) FROM  prd_data pd2 WHERE pd2.wid = prd_data.wid ORDER BY "date" LIMIT 6) as
tot_oil,(SELECTSUM(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;

However, that will be querying the prd_data table about (distinct wid)*2+1 
times.   Don't hold your breath.

> 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.

It might.  Not for the summaries themselves (which are fastest as aggregates), 
but to build a table that has only 6 records per WID.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco