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'
