Steve Stedman wrote: > 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 think you mean: SELECT count(*) AS total, pagename AS page, username AS uname FROM trackRes WHERE username NOT LIKE '%cir-inc.com%' GROUP BY pagename, username ORDER BY count(*) DESC Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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 Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
