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

Question

Having a table with a colum 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 1

Cast the datetime to a date, then group my that:

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 for performance, it is a little clearer.

Answer 2

Or:

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

More efficient (I think.) Because you don't have to cast mydate twice per row.