Subject: Re: SQL subquery (count distinct) - Any Ideas?
From: Rod Taylor <pg@rbt.ca>
To: Matt Fulford <mtfulford@yahoo.co.uk>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <Wzmdnb1DKJVDy8DfSa8jmA@karoo.co.uk>
References: <Wzmdnb1DKJVDy8DfSa8jmA@karoo.co.uk>
Content-Type: text/plain
Date: Mon, 18 Apr 2005 22:19:24 -0400
Message-Id: <1113877164.41948.16.camel@home>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2005-04-13 at 18:28 +0100, Matt Fulford wrote:
> I'm trying to write a query to return the number of different customers 
> called on a single day.  The database has a table called 'user', a table 
> called 'caller_session' which references a 'user' record, and a table called 
> 'call' which references a 'caller_session'.  This is my current attempt:

If I understand correct, this might work and still allow you to keep
your other subselects:

        SELECT tab.name,
        sum(contacted) AS contacted,
        ... subselects ...
        FROM (SELECT user_id, user.name, count(*) AS contacted
                FROM user JOIN caller_session USING (user_id)
                JOIN call USING (caller_session_id)
                WHERE date(cs.session_date) = date('2005-04-13')
                GROUP BY user_id, user.name) AS tab
        JOIN caller_session USING (user_id)
        GROUP BY user_id;

> select
> user.name,
> sum((select count(distinct call.customer_id) from call where 
> call.caller_session_id=cs.caller_session_id)) as contacted
> from user, caller_session cs
> where cs.user_id=user.user_id
> and date(cs.session_date) = date('2005-04-13')
> group by user.name;
> 
> I get back a list of names and a call count (as desired), but the count is 
> not correct!  The subqery is counting the number of different customers 
> called for each caller_session, and summing them based on the user.name 
> field.  If the same customer is called in 2 different sessions, this is 
> being counted as 2 customers, but should be 1!
> 
> The 'contacted' count calculation has to be in a subquery as above, because 
> there are other subqueries after this one to calculate other data based on 
> the outer query (the outer query has to be as it is here).  Not quite sure 
> where the sum, count and distinct should really go to get the required 
> results!
> 
> Any help greatly appreciated!
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 
--