Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.1) with ESMTP id IAA82412 for ; Tue, 27 Apr 1999 08:58:37 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id OAA00540; Tue, 27 Apr 1999 14:58:07 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10c9Bl-0007VZ-00; Tue, 27 Apr 1999 14:50:21 +0000 Message-ID: <3725B28C.AE3D4406@sferacarta.com> Date: Tue, 27 Apr 1999 14:50:20 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Nuchanard Chiannilkulchai CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Percentages? References: <371F2ED6.29FD9251@scigen.co.uk> <3721B398.DF32C1EC@bigfoot.com> <37257333.522DF88A@valigene.com> Content-Type: multipart/mixed; boundary="------------883488D8F5AA081423B2D0DD" Questo è un messaggio multi-parte scritto in formato MIME. --------------883488D8F5AA081423B2D0DD Content-Type: multipart/alternative; boundary="------------D162ECDF200540261CD39D65" --------------D162ECDF200540261CD39D65 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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é --------------D162ECDF200540261CD39D65 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  

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é
 
 
 
 
  --------------D162ECDF200540261CD39D65-- --------------883488D8F5AA081423B2D0DD Content-Type: text/plain; charset=us-ascii; name="format.sql" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="format.sql" -- 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,'###,###'); --------------883488D8F5AA081423B2D0DD Content-Type: text/plain; charset=us-ascii; name="text_float.sql" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="text_float.sql" -- 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; --------------883488D8F5AA081423B2D0DD--