Skip site navigation (1) Skip section navigation (2)

SQL Count Magic Required....

From: <operationsengineer1(at)yahoo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: SQL Count Magic Required....
Date: 2006-05-31 23:32:43
Message-ID: 20060531233243.34255.qmail@web33307.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
i have the following query that yields a series of
true or false results:

-- distinct on is pgsql extension --
SELECT DISTINCT ON (t_inspect_result.inspect_id)
t_inspect_result.inspect_result_id, 
                  
t_inspect_result.inspect_result_pass, 
                   t_inspect_area.inspect_area,
                  
t_inspect_result.inspect_result_timestamp
                   --,t_inspect.serial_number_id,
t_inspect.inspect_id
FROM t_inspect_result, t_inspect, t_inspect_area,
     t_serial_number,
t_link_contract_number_job_number, t_job_number,
t_product
WHERE  t_inspect.inspect_area_id =
       t_inspect_area.inspect_area_id
AND t_inspect.serial_number_id =
    t_serial_number.serial_number_id
AND t_serial_number.link_contract_number_job_number_id
=
   
t_link_contract_number_job_number.link_contract_number_job_number_id
AND t_link_contract_number_job_number.job_number_id =
    t_job_number.job_number_id
AND t_product.product_id =
    t_job_number.product_id
AND t_inspect.inspect_id =
    t_inspect_result.inspect_id
AND t_inspect.serial_number_id = '200'
ORDER BY t_inspect_result.inspect_id DESC,
t_inspect_result.inspect_result_timestamp ASC
-- used to get first pass yield pass / fail (true /
false) data.
-- inspect_id desc impacts end result.  time desc
impacts the groups prior to being distinctly listed

the simplified output may look like

f,t,t,f,f,f,t,f,t,f

the COUNT magic comes into play b/c i want to count
the result set's "t"s and total, but i have no clue
how to get this done.

#ts: 4
#total: 10

when i have this data, i can apply some math and come
up with a 40% yield.

any help is, as always, much appreciated.

tia...

ps - this query looks to be brutal when there is a lot
of data.  is it?  if so, how can i optimize it?

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

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2006-05-31 23:58:36
Subject: Re: searching multiple tables
Previous:From: Richard Broersma JrDate: 2006-05-31 22:40:20
Subject: Re: searching multiple tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group