CODEWITHSUNDEEP
sqlmysqlgroup-by
waxical
waxical

Reputation: 3896

Grouping result by date in mysql

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.

Upvotes: 0

Views: 296

Answers (2)

Welbog
Welbog

Reputation: 60378

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`)

Upvotes: 4

Schamp
Schamp

Reputation: 302

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?

Upvotes: 0

Related Questions