Using Group By in MYSQL while using distinct on another column

I have a table which stores every click I send to other websites. It contains the ID of the website Im sending the click to, the datetime of the click, the IP addresss of the click and the region that IP address is from (IE: USA, Canada, UK, etc.)

When trying to see how many clicks I've sent to a website from each region for the day I u开发者_运维知识库se a query like so...

SELECT  `clicks_region`, COUNT(*) as TOTAL 
FROM `clicks` 
WHERE `clicks_to_websites_id` = '1' 
AND DATE(`clicks_date`) = DATE('2011-08-30' )
GROUP BY `clicks_region`

Now I want to do the same query but limit it so an IP will only count once per day

My first thought was to do something like this...

 SELECT  `clicks_region`, COUNT(*) as TOTAL 
 FROM `clicks` 
 WHERE `clicks_to_websites_id` = '1' 
 AND DATE(`clicks_date`) = DATE('2011-08-30' )
 GROUP BY `clicks_region`
 HAVING DISTINCT(`clicks_ip`)

But obviously that syntax doesn't work.

I've been unable to find a solution thus far in my research. Im beginning to think a sub-query may be the answer - if anyone has suggestions or advice it'd be much appreciated.

Thanks

If I understand you correctly, I think you want something like this:

SELECT  `clicks_region`, COUNT(*) as TOTAL 
 FROM 
    (SELECT DISTINCT 'clicks_region', 'clicks_ip' 
     FROM 'clicks'
     WHERE `clicks_to_websites_id` = '1' 
     AND DATE(`clicks_date`) = DATE('2011-08-30' )) x
 GROUP BY `clicks_region`