MySQL SQL Group by date only on a Datetime column

Question

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

User · Accepted 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

User · Answer

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

User · Answer

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

User · Answer

I found that I needed to group by the month and year so neither of the above worked for me  Instead I used date format  SELECT date FROM blog  GROUP BY DATE FORMAT date    m- y   ORDER BY YEAR date  DESC  MONTH date  DESC

User · Answer

SELECT SUM No   HOUR dateofissue   FROM tablename  WHERE dateofissue gt   2011-07-30   GROUP BY HOUR dateofissue    It will give the hour by sum from a particular day

User · Answer

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

User · Answer

I found that I needed to group by the month and year so neither of the above worked for me  Instead I used date format  SELECT date FROM blog  GROUP BY DATE FORMAT date    m- y   ORDER BY YEAR date  DESC  MONTH date  DESC

User · Answer

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

User · Answer

SELECT SUM No   HOUR dateofissue   FROM tablename  WHERE dateofissue gt   2011-07-30   GROUP BY HOUR dateofissue    It will give the hour by sum from a particular day

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

Examples related to sql

Examples related to mysql