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