I have a mysql column where the data is stored as VARCHAR though the data values are of datetime in the format of yyyy-mm-dd hh:mm:ss.
Now my task is to group by the date part i.e yyyy-mm-dd by converting VARCHAR to date-time and then just taking date part out of it
QUERY
SELECT SUM(value)
FROM table
GROUP BY name , [date part of the varchar field]
Please let me know if this is at all possible and if yes, how?
Assuming that your data in this varchar field is properly formatted, you can work with the left function, like this:
SELECT LEFT(mydate, 10) AS myval,
SUM(myvalue)
FROM mytable
GROUP BY myval;
If this isn't a big issue; I'd advise converting your varchar column to datetime or timestamp. If not only for the possibly better data storage usage, it'll be way easier to do work with date and time related functions.
datetime is mostly my choice. Read about this here and make up your mind. dev.mysql.com/doc/refman/5.7/en/date-and-time-types.htmlJust use the left function. You can leave the date as a string:
SELECT left(datecol, 10) as YYYYMMDD, SUM(value)
FROM table
GROUP BY left(datecol, 10);
I removed name from the group by because it doesn't seem relevant to the question. You can, of course, add it back in.
By the way, MySQL understands this format for dates, so if you really, really want a date:
SELECT date(left(datecol, 10)) as RealDate, SUM(value)
FROM table
GROUP BY RealDate;
