Re: multiple paramters in aggregate function

On 13 Aug 2009, at 12:51, Sim Zacks wrote:

> What I am trying to do is sum a quantity field, but it also has units
> that need to be converted.

> 4 meter
>
> 400 mm
>
> 100 cm
>
>
> I want to sum it all, my function decides to use meter (based on the
> requirements) and should return 4.00104 (or something like that) and
> then I have a second aggregate function which just chooses which
> unit to
> use, so in my query I use 2 aggregate functions, one gives me the
> sum of
> converted quantity and the other gives me which unit it is in.

> Is there a better way?

It's probably easiest to decide on an internal unit to use in your
aggregate and only convert it to the desired unit once you're done
summing them. I'd probably convert all measurements to mm in the
function and summarise those.

The final unit conversion can be taken out of the aggregate that way
too, so I'd also have separate functions for converting units to and
from other units - those functions will likely come in handy anyway.

Your query would then be something like:
SELECT convert_unit(sum_mm(field), 'mm', 'meter') FROM table;

In general, don't put multiple operations in one function but split
them into separate functions. You're much more flexible that way.

Alban Hertroys

--
Screwing up is the correct approach to attaching something to the
ceiling.

!DSPAM:737,4a83fca210137297812668!