On Tue, Mar 21, 2000 at 04:07:20PM -0800, Arthur M. Kang wrote:
> Was wondering if there was a way to use an aggregate value in a single
> select query to make a calculation within that query...
>
> Example:
> Table has column bool of type boolean with various random boolean
> values.
>
> Want to see if it is possible (in a single select query) to find the
> percentage of entries in the table that are true.
>
> (SELECT count(*) FROM table WHERE bool='t') / (SELECT count(*) FROM
> table)
>
Very close: here's with current CVS sources (should be released April 1
as 7.0)
reedstrm=# select count(*) from test;
count
-------
10
(1 row)
reedstrm=# select a,count(*) from test group by a;
a | count
---+-------
f | 5
t | 5
(2 rows)
reedstrm=# select a,count(*),(count(*)*100.00)/(select count(*) from test)
reedstrm-# from test group by a;
a | count | ?column?
---+-------+----------
f | 5 | 50
t | 5 | 50
(2 rows)
You an throw a WHERE clause in the outer query, if you want to restrict
which results get returned.
However, this _doesn't_ work in 6.5.X (or even 7.0beta2: but that was a bug)
So, in current stable, no you can't do it in one query. In the coming stable,
you sure can!
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
