pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL subquery (count distinct) - Any Ideas?
3+ messages / 3 participants
[nested] [flat]
* SQL subquery (count distinct) - Any Ideas?
@ 2005-04-13 17:28 Matt Fulford <mtfulford@yahoo.co.uk>
0 siblings, 1 reply; 3+ messages in thread
From: Matt Fulford @ 2005-04-13 17:28 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SQL subquery (count distinct) - Any Ideas?
@ 2005-04-19 02:19 Rod Taylor <pg@rbt.ca>
parent: Matt Fulford <mtfulford@yahoo.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Rod Taylor @ 2005-04-19 02:19 UTC (permalink / raw)
To: Matt Fulford <mtfulford@yahoo.co.uk>; +Cc: pgsql-sql
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
>
--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SQL subquery (count distinct) - Any Ideas?
@ 2005-04-19 07:12 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-04-19 07:12 UTC (permalink / raw)
To: Matt Fulford <mtfulford@yahoo.co.uk>; pgsql-sql
Hi
I have modified the SQL query given by you and I hope the query given below will give you the correct count.
SELECT U.USER_ID , U.NAME , COUNT(*) FROM USER U , CALL C , CALLER_SESSION CS
WHERE C.CALLER_SESSION_ID = CS.CALLER_SESSION_ID AND U.USER_ID = CS.USER_ID AND CAST( CS.SESSION_DATE AS DATE ) = CAST( '2005-04-13' AS DATE )
GROUP BY U.USER_ID
Regards,
R.Muralidharan
-----Original Message-----
From: Matt Fulford [mailto:mtfulford@yahoo.co.uk]
Sent: Wednesday, April 13, 2005 10:59 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL subquery (count distinct) - Any Ideas?
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!
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-04-19 07:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-13 17:28 SQL subquery (count distinct) - Any Ideas? Matt Fulford <mtfulford@yahoo.co.uk>
2005-04-19 02:19 ` Rod Taylor <pg@rbt.ca>
2005-04-19 07:12 Re: SQL subquery (count distinct) - Any Ideas? Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
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