Re: Aggregate query for multiple records

Scott Gerhardt <scott@g-it.ca> writes:

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

Not only is this on-charter, but this specific question comes up fairly often.

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

This type of "top 6" or in this case "first 6" query is pretty tricky to do in
SQL. In fact the best solution anyone's proposed here uses non-standard
postgres extensions to define an aggregate that keeps an accumulation in an
array.

Something like (but I suppose you need reals, not integers):

test=> create or replace function first_6_accum (integer[], integer) returns integer[]
         language sql immutable as
        'select case when array_upper($1,1)>=6 then $1 else $1||$2 end';

test=> create function sum_6(integer[]) returns integer
         immutable language sql as
        'select $1[1]+$1[2]+$1[3]+$1[4]+$1[5]+$1[6]';

test=> create aggregate sum_first_6
         (basetype=integer, sfunc=first_6_accum, stype=integer[],initcond='{}',finalfunc=sum_6);

test=> select sum_first_6(i) from (select i from (select 1 as i union select 2 union select 3 union select 4 union
select5 union select 6 union select 7 union select 8) as x order by i asc) as x; 
 sum_first_6
-------------
          21
(1 row)

You'll need to select from a subquery that guarantees the correct ordering.
And then you'll need to do a GROUP BY wid. And then you should be aware that
some versions of postgres didn't always use a sorting method for the group by
that guaranteed the ordering of the subquery was preserved. I think you're
safe in 7.4 but you would have to test it.

--
greg