From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Calculating percentages in Postgresql |
Date: | 2006-11-21 14:32:22 |
Message-ID: | 20061121143222.GE24314@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
am Tue, dem 21.11.2006, um 14:59:16 +0200 mailte Peter Nixonn folgendes:
> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR: division by zero" when fed zeros?
Quick & simple:
create or replace function percentage(float,float) returns float as $$
begin
if $2 = 0 then
return NULL;
else
return 100*$1/$2;
end if;
end;
$$ language plpgsql immutable;
Example:
test=*> select percentage(5,10);
percentage
------------
50
(1 row)
test=*> select percentage(5,0);
percentage
------------
(1 row)
test=*> select coalesce(percentage(5,0),0);
coalesce
----------
0
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From | Date | Subject | |
---|---|---|---|
Next Message | Jerry Sievers | 2006-11-21 14:38:52 | Re: Calculating percentages in Postgresql |
Previous Message | Richard Huxton | 2006-11-21 14:22:59 | Re: Calculating percentages in Postgresql |