From: proghome@silesky.com (krystoffff)
Subject: Re: How to optimize this query ?
Date: 14 Aug 2003 11:14:46 -0700
Organization: http://groups.google.com/
Lines: 59
Message-ID: <85898f7e.0308141014.423d0211@posting.google.com>
References: <001a01c361c4$7d5d59e0$0700a8c0@Office3>
	<20030813112311.J53899-100000@megazone.bigpanda.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

For the following query, I have a little problem ...
First, I have to rewrite several times the same query because the
alias are not recognised in the same query ( I got an error when I try
to reuse the alias "nb_bogus_leads", for instance). Do you have a way
to avoid this ? Because If I do so, the same query is calculated twice
...

Second problem, the most important :
The A.id should be for each result returned in A.*, and there should
be a join to calculate the query "nb_bogus_leads" (for instance) about
the A.id currently processed by the query.
But it seems that this join doesn't work, because I have the same
"nb_bogus_leads" and same "nb_leads_submitted" for each A.id returned
(they should be different !)

How can you make this query work ?
Thanks


SELECT A. * , (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM request
INNER JOIN lead ON ( lead_id = lead.id ) 
WHERE allowed = 1 AND lead.affiliate_id = A.id
) AS nb_bogus_leads, (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) AS nb_leads_submitted, (

CASE WHEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) <> 0
THEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM request
INNER JOIN lead ON ( lead_id = lead.id ) 
WHERE allowed = 1 AND lead.affiliate_id = A.id
) / ( 
SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id ) * 100
WHEN (

SELECT CAST( count( * ) AS UNSIGNED ) 
FROM lead
WHERE affiliate_id = A.id
) = 0
THEN 0 
END 
) AS percentage_bogus_leads
FROM affiliate A
WHERE website = 'dev'