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:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
