DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=vtF6etj2Dl38dKoqzBHBJNsiR0XKw+Gt2+U8Jty2/Ay2DsvSn9PrU6o59I1l+9UwrDx7yzG2Waj3MEuy/0OvDIbiMCogHEMdyW6V8mi62Ej1EVRypomkM6wAzV6Dq07Fxkdhd+zoGY2LXhYW5z61vARNIENgYpR8aG+MiImw4TM=
	;
Message-ID: <20060602003025.5985.qmail@web33315.mail.mud.yahoo.com>
Date: Thu, 1 Jun 2006 17:30:25 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Re: Advanced Query
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20060602000051.GA12215@winnie.fuhr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> 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