Re: Advanced Query

> On Thu, Jun 01, 2006 at 04:09:21PM -0700,
> operationsengineer1(at)yahoo(dot)com wrote:
> > what i can't seem to do is to get both - a count
> of
> > the total number of t_inspect_result.inspect_pass
> > where the value is true and a total count, by
> unique
> > t_inspect.id.
>
> Are you looking for something like this?
>
> SELECT 1.0 * sum(CASE WHEN inspect_pass THEN 1 ELSE
> 0 END) / count(*)
> FROM (
> SELECT DISTINCT ON (inspect_id) inspect_id,
> inspect_pass
> FROM t_inspect_result
> ORDER BY inspect_id, id
> ) AS s;
>
> Multiply by 100.0 instead of 1.0 if you want
> percent.
>
> If you have a cast from boolean to integer (built-in
> in 8.1, easily
> created in earlier versions) then you could replace
> the CASE
> expression with a cast (inspect_pass::integer).
> Whether to use the
> more explicit CASE or the more concise cast is a
> matter of style.

Michael, wow! i never heard of case or seen the "if /
then" style in sql. i need to get out more... or
maybe less. ;-)

i have been working through a simplified version of
the problem and i am accurately getting the "pieces"
of data that i need (#pass, #total) - it is similar to
your example following your first FROM statement.

i just need to work the complexities back in w/o
destroying my current results and then perform the
math on the results - either in pgsql or in my app.

i'll play around with the more advanced stuff
tomorrow.

thanks - i think i have enough pieces to get this
done. if not... "i'll be baaawck."

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com