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;
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