Postgres Pro
Downloads
Home   >   mailing lists

Thread: Calculating percentages in Postgresql

Calculating percentages in Postgresql

From
Peter Nixonn
Date:
Hi Guys


Has anyone written a postgres function to calculate percentages without
giving "ERROR:  division by zero" when fed zeros?

TIA

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc

Re: Calculating percentages in Postgresql

From
Richard Huxton
Date:
Peter Nixonn wrote:
> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR:  division by zero" when fed zeros?

Almost certainly. The question is, what do *you* want it to do?

You'll want to customise something like:

CREATE FUNCTION percent_plus(int4, int4) RETURNS int4 AS $$
   SELECT CASE WHEN $2=0 THEN -1 ELSE ($1*100)/$2 END;
$$ LANGUAGE SQL;

--
   Richard Huxton
   Archonet Ltd

Re: Calculating percentages in Postgresql

From
"A. Kretschmer"
Date:
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

Re: Calculating percentages in Postgresql

From
Jerry Sievers
Date:
Peter Nixonn <listuser@peternixon.net> writes:

> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR:  division by zero" when fed zeros?
>

One simple way to hack around this is wrap the problematic operand in
'nullif' and let it compute a null result where div by zero is
avoided.

jerry@jerry#
= \pset null 'Oops!'
Null display is "Oops!".
jerry@jerry#
= select 100 / nullif(num, 0) from generate_series(0,3) foo (num);
 ?column?
----------
    Oops!
      100
       50
       33
(4 rows)

jerry@jerry#
=

> --
>
> Peter Nixon
> http://www.peternixon.net/
> PGP Key: http://www.peternixon.net/public.asc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     Production Database Administrator
                305 321-1144 (mobil    WWW E-Commerce Consultant

Re: Calculating percentages in Postgresql

From
Tino Wildenhain
Date:
Peter Nixonn schrieb:
> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR:  division by zero" when fed zeros?
>
What is the expected result when you feed zeros?
What is your use-case? 0 out of 0 or something?
Should that mean 100% or 0%? :-) Or just: Null
(as "dont't know")

Its easy when you fully know your problem :-)

Regards
Tino

Re: Calculating percentages in Postgresql

From
Richard Broersma Jr
Date:
--- Peter Nixonn <listuser@peternixon.net> wrote:

> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR:  division by zero" when fed zeros?

maybe you could employee the nullif() function to trap zeros for this?
http://www.postgresql.org/docs/8.2/interactive/functions-conditional.html#AEN13119

Regards,

Richard Broersma Jr.