Re: [SQL] Percentages?

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(dot)bitmead(at)bigfoot(dot)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é