Re: SQL subquery (count distinct) - Any Ideas?

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
> 
--