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
