From: Rod Taylor <pg@rbt.ca>
To: Matt Fulford <mtfulford@yahoo.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL subquery (count distinct) - Any Ideas?
Date: Mon, 18 Apr 2005 22:19:24 -0400
Message-ID: <1113877164.41948.16.camel@home> (raw)
In-Reply-To: <Wzmdnb1DKJVDy8DfSa8jmA@karoo.co.uk>
References: <Wzmdnb1DKJVDy8DfSa8jmA@karoo.co.uk>
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
>
--
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: pg@rbt.ca, mtfulford@yahoo.co.uk
Subject: Re: SQL subquery (count distinct) - Any Ideas?
In-Reply-To: <1113877164.41948.16.camel@home>
* 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