Re: Advanced Query

> On Thu, Jun 01, 2006 at 04:09:21PM -0700,
> operationsengineer1@yahoo.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