Group by date, not date and time

[epaisley]

I have a datetime field, entryDate. I need to group by just the date of the date time. So it groups all the records from the same day together. What is the best way to do this?

[lilleman]

Hi

Try this:

SELECT * FROM table GROUP BY DATE_FORMAT(entryDate, '%Y-%m-%d')

/Erik

[Betcour]

...or :

SELECT * FROM table GROUP BY TO_DAYS(entryDate)

[r937]

please don't say "select star" when you use GROUP BY

you could get unpredictable results

[lilleman]

r937, what do you mean?

Yours, Erik.

[r937]

because it's illegal in all other databases, and can produce unpredictable results in mysql

[lilleman]

Hi

How should the query look to be correct?

Yours, Erik.

[r937]

select date_format(entryDate,"%Y-%m-%d") as printDate
     , count(*) as rows
  from yourtable
group
    by date_format(entryDate,"%Y-%m-%d")

[Claymore]

please don't say "select star" when you use GROUP BY

you could get unpredictable results

Does that also apply to ORDER BY, or is just a problem inherent to queries using the GROUP BY command?

[r937]

just GROUP BY

the deal is, every non-aggregate column in the SELECT must be in the GROUP BY, and vice versa

but the columns in the ORDER BY can be a subset of the columns in the SELECT list, and the ORDER BY can also include aggregates

this means you can say "select star" with or without an ORDER BY

[Claymore]

Okay, thanks for clearing that up.