X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B0BBE476624
	for <pgsql-sql@postgresql.org>; Thu, 15 May 2003 05:04:54 -0400 (EDT)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	ESMTP id 58984-09 for <pgsql-sql@postgresql.org>;
	Thu, 15 May 2003 05:04:52 -0400 (EDT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by postgresql.org (Postfix) with SMTP id B1400476609
	for <pgsql-sql@postgresql.org>; Thu, 15 May 2003 05:04:51 -0400 (EDT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Thu May 15 11:05:01 2003 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id LAA05024; Thu, 15 May 2003 11:04:51 +0200 (METDST)
Message-ID: <3EC35832.8BA90263@rodos.fzk.de>
Date: Thu, 15 May 2003 11:04:50 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: dsiegal@brave.cs.uml.edu
Subject: Re: Calculating Percentages
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200305/311
X-Sequence-Number: 13476

>
> SELECT nationality, (COUNT(*)*100/total_members) as percentage
> FROM member,
>       (SELECT COUNT(*) as total_members FROM members) tot_mem
> GROUP BY nationality
> ORDER BY nationality
>
You'll have to GROUP BY nationality, total_members

Remember COUNT returns bigint, so percentage is the
result of an integer division. To obtain floating point results use
(COUNT(*)*100.0/total_members)

> > If I were to do:
>
> > SELECT nationality, ((COUNT(*) * 100)/(select count(*) from member))
as
> > percentage FROM member GROUP BY nationality ORDER BY nationality;
>
> > would this repeatedly execute the inner query over and over?
>
> No, it'd do it just once, because the sub-select has no dependency on
> the outer select.  (If the sub-select used any variables from the
outer
> level, then it'd have to be done over at each outer row.)
>
Really nice piece of analyzer.

Regards, Christoph