fmsf fmsf - 1 year ago 107
MySQL Question

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

Having a table with a column like:


I have a query such as:

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

This will group by the full
, including hours and minutes. I wish to make the group by, only by the date
not by the

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download