The Little Guy Posted January 13, 2009 Share Posted January 13, 2009 I need to get a count from the database and group them by: - Monday - Tuesday - Wednesday - Thursday - Friday - Saturday - Sunday I have a field called "date" which is a time stamp, and this is the one I want to group by. I also want to ONLY include the last 30 days in this selection. How can I do that? Quote Link to comment Share on other sites More sharing options...
xtopolis Posted January 13, 2009 Share Posted January 13, 2009 SELECT DATE_FORMAT(entryTS, '%a') AS 'Day of Week', count(1) AS 'Count' FROM test WHERE entryTS >= DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY 1 ORDER BY DAYOFWEEK(entryTS) I found this on http://lists.mysql.com/mysql/183242 It provides an explanation there. Quote Link to comment Share on other sites More sharing options...
corbin Posted January 13, 2009 Share Posted January 13, 2009 Edit: xtropolis beat me..... Posting it anyway ;p. ORDER BY DAYOFWEEK(column_name); Should do it. Oh, and for the 30 days thing.... WHERE column_name > DATE_SUB(NOW(), INTERVAL 30 DAYS) Or when you said timestamp did you mean unix timestamp? Quote Link to comment Share on other sites More sharing options...
xtopolis Posted January 13, 2009 Share Posted January 13, 2009 I wouldn't mind it that much if you didn't call me xtropolis wtf is an xtRopolis? Quote Link to comment Share on other sites More sharing options...
corbin Posted January 13, 2009 Share Posted January 13, 2009 Hrmmm.... Wow..... That's awkward.... I'm entirely retarded, apparently. I've no idea why I've been adding an r into your name for the past like 3 months. x.x. wtf is an xtRopolis? What is an xtopolis? Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted January 13, 2009 Author Share Posted January 13, 2009 Thanks! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.