Grouping result by date in mysql

Question

This SQL statement

SELECT `ip`, `when` FROM `metrics` WHERE `vidID` = '1' GROUP BY DATE('when')

returns one result, even though multiple are present in the table with different dates. I've tried using DATE_FORMAT as well. Am I doing something stupid?

When is a timestamp column with full timestamp, including hours, minutes and seconds. I'm trying to just group by results by day/month/year.

Answer 1

Looks like you're grouping by the constant string 'when' instead of the field when.

Use this instead:

GROUP BY DATE(`when`)

Sounds like you want to count the IP addresses for a given date:

SELECT COUNT(`ip`) AddressCount, `when`
FROM `metrics` 
WHERE `vidID` = '1' 
GROUP BY DATE(`when`)

Answer 2

Usually, GROUP BY is used in conjunction with some aggregate function (like SUM, say) to compute the result set by one or more columns. What are you trying to accomplish here? Do you mean to sort, or just get a collapsed list of which IPs have records on which dates?