Re: How to optimize this query ?

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'