From: Matt Fulford <mtfulford@yahoo.co.uk>
To: pgsql-sql@postgresql.org
Subject: SQL subquery (count distinct) - Any Ideas?
Date: Wed, 13 Apr 2005 18:28:36 +0100
Message-ID: <Wzmdnb1DKJVDy8DfSa8jmA@karoo.co.uk> (raw)
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:
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!
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: mtfulford@yahoo.co.uk
Subject: Re: SQL subquery (count distinct) - Any Ideas?
In-Reply-To: <Wzmdnb1DKJVDy8DfSa8jmA@karoo.co.uk>
* 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