I need to output a query with a count of pagename and unique pagenames. The following SQL works fine for this:
SELECT count(*) AS total, pagename AS page FROM trackRes WHERE username NOT LIKE '%cir-inc.com%' GROUP BY pagename ORDER BY count(*) DESC However, I need to get output this data with distinct usernames. Using the example of the table data below, I would like to see a count of 2 for page1 and 1 for page2. What I'm getting, of course, is a count of 4 for page1: id pagename username == ======== ======== 1 page1 name1 2 page2 name1 3 page1 name2 4 page1 name1 5 page1 name2 I've tried the following SQL, but it doesn't work. Help! SELECT count(DISTINCT username) AS total, pagename AS page FROM trackRes WHERE username NOT LIKE '%cir-inc.com%' GROUP BY pagename ORDER BY count(DISTINCT username) DESC BTW, we're running CF5 with Access as the database. Thanks, Steve Stedman [EMAIL PROTECTED] ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq 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 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
