getting non-distinct results from a distinct mysql query

Question

Right, another question on queries (there must be a syntax guide more helpful than mySQL's manual, surely?)

I have this query (from another helpful answer on SO)...

SELECT DATE_FORMAT(`when`, '%e_%c_%Y')date, COUNT(`ip`) AddressCount FROM `Metrics` WHERE `ID` = '1' GROUP BY DATE(`when`)

I now want to do a similar query to get unique/distinct results for the IPs... i.e. unique visitors per date. My query was this...

SELECT DATE_FORMAT(`when`, '%e_%c_%Y')date, COUNT(distinct `ip`) AddressCount FROM `Metrics` WHERE `ID` = '1' GROUP BY DATE(`when`)

However, that returns a repetition of dates, though different quantities of Addresscount...

date    AddressCount
29_6_2009   1
30_6_2009   1
29_6_2009   1
30_6_2009   1
29_6_2009   1
NULL    1
15_5_2009   1
14_5_2009   2
NULL    3
14_5_2009   4
15_5_2009   1
26_6_2009   1
29_6_2009   1
26_6_2009   1
15_5_2009   1
26_6_2009   1
29_6_2009   1

Any ideas on where I'm going wrong?

Answer 1

Your group by will need to match the data you're selecting, so this should work:

SELECT DATE_FORMAT(`when`, '%e_%c_%Y')date, COUNT(distinct `ip`) AddressCount FROM `Metrics` WHERE `ID` = '1' GROUP BY date

Answer 2

Try

SELECT DATE_FORMAT(when, '%e_%c_%Y')date, COUNT(distinct ip) AddressCount FROM Metrics WHERE ID = '1' GROUP BY date(when)

You might have run into some bugs when using reserved words in MySQL