pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPercentages?
3+ messages / 3 participants
[nested] [flat]
* Percentages?
@ 1999-04-24 12:05 Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 1 reply; 3+ messages in thread
From: Chris Bitmead @ 1999-04-24 12:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Percentages?
@ 1999-04-27 08:20 Nuchanard Chiannilkulchai <nuch@valigene.com>
parent: Chris Bitmead <chris.bitmead@bigfoot.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nuchanard Chiannilkulchai @ 1999-04-27 08:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Percentages?
@ 1999-04-27 12:50 José Soares <jose@sferacarta.com>
parent: Nuchanard Chiannilkulchai <nuch@valigene.com>
0 siblings, 0 replies; 3+ messages in thread
From: José Soares @ 1999-04-27 12:50 UTC (permalink / raw)
To: Nuchanard Chiannilkulchai <nuch@valigene.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-04-27 12:50 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-24 12:05 Percentages? Chris Bitmead <chris.bitmead@bigfoot.com>
1999-04-27 08:20 ` Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-27 12:50 ` José Soares <jose@sferacarta.com>
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