pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: José Soares <jose@sferacarta.com>
To: Nuchanard Chiannilkulchai <nuch@valigene.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Percentages?
Date: Tue, 27 Apr 1999 14:50:20 +0200
Message-ID: <3725B28C.AE3D4406@sferacarta.com> (raw)
References: <371F2ED6.29FD9251@scigen.co.uk>
	<3721B398.DF32C1EC@bigfoot.com>
	<37257333.522DF88A@valigene.com>



Nuchanard Chiannilkulchai ha scritto:

> Chris Bitmead wrote:
>
> > How to calculate percentages? What is the correct SQL?
> >
> > CREATE TABLE poll (candidate text, votes int4);
> >
> > I want to do something like (this is wrong)...
> > SELECT candidate, votes, (votes / sum(votes)) * 100) AS percent FROM
> > poll;
> >
> > Fred Smith  |  500 | 25
> > Bill Bloggs | 1000 | 50
> > Jim Jones   |  500 | 25
> >
> > --
> > Chris Bitmead
> > http://www.bigfoot.com/~chris.bitmead
> > mailto:chris.bitmead@bigfoot.com
>
> I always solve by a temporary table
>
> select sum(votes) as sumvotes  into tmp from poll;
> SELECT candidate, votes, (float4(votes)/float4(sumvotes))*100
> as percent from poll, tmp;
> candidate|votes|         percent
> ---------+-----+----------------
> Fred     |  500|23.8095238804817
> Bill     | 1000|47.6190477609634
> James    |  600| 28.571429848671
> (3 rows)
>
> The problem is now cutting to only to 2 decimal point (ie:  23.80,
> 47.61, 28.57)
> and we need some further help.
>
> Nuch

In v6.5 you can use decimal type.
otherwise try this:

select candidate, votes, substr(cast(percents as text),1,position('.' in
cast(pe
rcents as text))) ||
substr(cast(percents as text),position('.' in cast(percents as text)) + 1,
2) as
 percent from test;
candidate|votes|percent
---------+-----+-------
Fred     |  500|  23.80
Bill     | 1000|  47.61
James    |  600|  28.57
(3 rows)

or this ...

select candidate, votes, format(percents,'###,##') from test;

candidate|votes|format
---------+-----+------
Fred     |  500| 23,80
Bill     | 1000| 47,61
James    |  600| 28,57
(3 rows)

-- NB: Here in Italy we use comma instead of decimal point.

Attached file contains:
format() function
text(float8) function  (to cast percents to text)


José

Attachments:

format.sql (text/plain, 3.4K) download -- funzioni per la formattazione di interi e float drop function format(float4,text); create function format(float4,text) returns text as ' begin return format(float8($1),$2); end; ' language 'plpgsql'; drop function format(float8,text); create function format(float8,text) returns text as 'declare fbak text; vbak int8; out text; fout text; lh text; res1 int8; res float8; i int2; i1 int2; df int2; sval text; begin vbak:= dtrunc($1); fbak:= $2; res:=$1; if $1 < 0 then res := -(res); else res := res; end if; df:= textlen(fbak); i:= textpos(fbak,'',''); if i > 0 then fbak:= substr(fbak,1,i - 1); end if; lh:= text(res); -- richiede la funzione text(float8) i1:= position(''.'' in lh); lh:= substr(lh,i1+1); fout:= format(vbak,fbak); if i = 0 then return fout; end if; out:= fout || ('',''); out:= out || (substr(lh || ''0000000000000000'',1,df - i)); return out; end; ' language 'plpgsql'; drop function format(int8,text); create function format(int8,text) returns text as 'declare fbak text; vbak int8; out text; sign int2; num char(1); car char(1); car1 char(1); car0 char(1); lf int2; bf int2; lv int2; sval text; begin vbak := $1; fbak := $2; if vbak < 0 then sign := 1; vbak := -(vbak); else sign := 0; end if; lf := textlen(fbak); bf := lf; sval := vbak; lv := textlen(sval); if lv > lf then raise exception ''the value % is greater than %'',$1,$2; end if; while (lv>0 or lf>0) loop car:= substr(fbak,lf,1); car0:= substr(fbak,lf+1,1); car1:= substr(fbak,lf - 1,1); if lv > 0 then if lf=0 then raise exception ''The value % is greater than %'',$1,$2; end if; if car = ''#'' or car = ''&'' then num := substr(sval,lv,1); out := substr(fbak,1,lf - 1) || num; if bf > lf then fbak := out || substr(fbak,lf+1); else fbak := out; end if; lf := lf - 1; lv := lv - 1; else lf := lf - 1; end if; else if sign = 1 then sign := 2; fbak := substr(fbak,1,lf - 1) || (''-'' || substr(fbak,lf + 1)); else if sign = 2 or car <> ''&'' then if car0 = ''-'' or car0 = '' '' then fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1)); else if car1 = ''#'' and (car <> ''#'' and car <> ''&'') then fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1)); else if car0 = ''#'' or car = ''#'' then fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1)); end if; end if; end if; else if car = ''&'' then if car0 <> '' '' then fbak := substr(fbak,1,lf - 1) || (''0'' || substr(fbak,lf + 1)); else fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1)); end if; end if; end if; end if; lf := lf - 1; end if; end loop; return fbak; end; ' language 'plpgsql'; drop function format(int4,text); create function format(int4,text) returns text as ' begin return format(int8($1),$2); end; ' language 'plpgsql'; drop function format(int2,text); create function format(int2,text) returns text as ' begin return format(int4($1),$2); end; ' language 'plpgsql'; select format(3112198,'(&&&)##-###'), format(311298,'##/##/19##'), format(3112198,'#''###''###'), format(3112198,'<&&&_&&&_&&&>'), format(311,'&''&&&''&&&'), format(56789.1234,'&.&&&.###,####'), format(3.454,'###,###'); select format(-3.004,'###,###');
text_float.sql (text/plain, 580B) download -- funzione per trasformare un text,char,varchar in un float... -- CAST(text AS float); drop function float8(text); create function float8(text) returns float8 as 'declare f float8; begin IF $1 ISNULL THEN RETURN ''0''; ELSE f:= trim($1); RETURN f; END IF; end; ' language 'plpgsql'; create table a (t text, c char(10), v varchar); insert into a values ('1.2','1.3','1.4'); insert into a values ('1.2'); select cast(t as float), cast(c as float), cast(v as float) from a; select * from a; select cast('12' as float); drop table a;

view thread (3+ messages)

Message-ID: <3725B28C.AE3D4406@sferacarta.com>
Permalink:  ../3725B28C.AE3D4406@sferacarta.com/
Also on:    postgresql.org/message-id/3725B28C.AE3D4406@sferacarta.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jose@sferacarta.com, nuch@valigene.com
  Subject: Re: [SQL] Percentages?
  In-Reply-To: <3725B28C.AE3D4406@sferacarta.com>
  References: <371F2ED6.29FD9251@scigen.co.uk> <3721B398.DF32C1EC@bigfoot.com> <37257333.522DF88A@valigene.com> <3725B28C.AE3D4406@sferacarta.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox