From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: dsiegal@brave.cs.uml.edu
Subject: Re: Calculating Percentages
Date: Thu, 15 May 2003 11:04:50 +0200
Message-ID: <3EC35832.8BA90263@rodos.fzk.de> (raw)
>
> 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, dsiegal@brave.cs.uml.edu
Subject: Re: Calculating Percentages
In-Reply-To: <3EC35832.8BA90263@rodos.fzk.de>
* 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