Try using HAVING: SELECT DISTINCT email + ' ' + pub AS DistinctValue, COUNT(DISTINCT email + ' ' + pub) AS DistinctCount, record_id, email, pub, status FROM opt_in_master GROUP BY email, pub, record_id, status HAVING COUNT(distinct email + ' ' + pub) = 1 ORDER BY email, pub, record_id, status
Not sure if I understood your query correctly, but give it a try... Thanks, Steve ------------------------------------- Steven Monaghan Oracle DBA / Cold Fusion Developer MSC Industrial Direct Co., Inc. http://www.mscdirect.com ------------------------------------- -----Original Message----- From: Susan H. Allen [mailto:[EMAIL PROTECTED] Sent: Friday, March 14, 2003 1:14 PM To: SQL Subject: Distinct Count I am trying to create a sql statement to do the following: table contains: pub email status opt_in_date select * from tablename where (select count(distinct field1 + ' ' + field2) > 1) and status = 1 order by email I know it's not that simple, I got as far as this: SELECT DISTINCT email + ' ' + pub AS DistinctValue, COUNT(DISTINCT email + ' ' + pub) AS DistinctCount, record_id, email, pub, status FROM opt_in_master WHERE (status = 1) GROUP BY email, pub, record_id, status ORDER BY email, pub, record_id, status but can't figure out how to select only records where DistinctCount > 1. Any pointers would be appreciated! TIA Susan H. Allen Web Applications Developer Pfingsten Publishing, L.L.C. Cleveland, Ohio ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
