pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nuchanard Chiannilkulchai <nuch@valigene.com>
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Percentages?
Date: Tue, 27 Apr 1999 10:20:03 +0200
Message-ID: <37257333.522DF88A@valigene.com> (raw)
References: <371F2ED6.29FD9251@scigen.co.uk>
	<3721B398.DF32C1EC@bigfoot.com>

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





view thread (3+ messages)  latest in thread

Message-ID: <37257333.522DF88A@valigene.com>
Permalink:  ../37257333.522DF88A@valigene.com/
Also on:    postgresql.org/message-id/37257333.522DF88A@valigene.com

 · 

reply

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: nuch@valigene.com
  Subject: Re: [SQL] Percentages?
  In-Reply-To: <37257333.522DF88A@valigene.com>
  References: <371F2ED6.29FD9251@scigen.co.uk> <3721B398.DF32C1EC@bigfoot.com> <37257333.522DF88A@valigene.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