fmsf fmsf - 3 months ago 10
MySQL Question

MySQL/SQL: Group by date only on a Datetime column

Having a table with a column like:

mydate DATETIME
...

I have a query such as:

SELECT SUM(foo), mydate FROM a_table GROUP BY a_table.mydate;


This will group by the full
datetime
, including hours and minutes. I wish to make the group by, only by the date
YYYY/MM/DD
not by the
YYYY/MM/DD/HH/mm
.

Anyone know how to do this? I can still do it (as I am atm), dynamically in my code, but I'm cleaning trash code and this can be made through the SQL I just can't find out how :(.

Answer

Cast the datetime to a date, then GROUP BY using this syntax:

SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);

Or you can GROUP BY the alias as @orlandu63 suggested:

SELECT SUM(foo), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly;

Though I don't think it'll make any difference to performance, it is a little clearer.