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' ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly