From: proghome@silesky.com
To: pgsql-sql@postgresql.org
Subject: Re: How to optimize this query ?
Date: 14 Aug 2003 11:14:46 -0700
Message-ID: <85898f7e.0308141014.423d0211@posting.google.com> (raw)
References: <001a01c361c4$7d5d59e0$0700a8c0@Office3>
<20030813112311.J53899-100000@megazone.bigpanda.com>
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'
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: proghome@silesky.com
Subject: Re: How to optimize this query ?
In-Reply-To: <85898f7e.0308141014.423d0211@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox