Received: from morgane.oleane.net (Morgane.OLEANE.Net [194.2.1.9]) by hub.org (8.9.3/8.9.1) with ESMTP id FAA51242 for ; Tue, 27 Apr 1999 05:12:55 -0400 (EDT) (envelope-from nuch@valigene.com) Received: from tom.oleane.net (root@tom.oleane.net [194.2.28.14]) by morgane.oleane.net with ESMTP id LAA08746 for ; Tue, 27 Apr 1999 11:12:53 +0200 Received: from valigene.com ([62.161.254.162]) by tom.oleane.net (8.8.8/8.8.8) with ESMTP id LAA07323 for ; Tue, 27 Apr 1999 11:12:52 +0200 Sender: nuch@tom.oleane.net Message-ID: <37257333.522DF88A@valigene.com> Date: Tue, 27 Apr 1999 10:20:03 +0200 From: Nuchanard Chiannilkulchai Organization: ValiGene X-Mailer: Mozilla 4.07 [en] (X11; I; Linux 2.0.36 i686) MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: [SQL] Percentages? References: <371F2ED6.29FD9251@scigen.co.uk> <3721B398.DF32C1EC@bigfoot.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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