select day(`timestamp`) as dayOfMonth, count(`timestamp`) as searchesPerDay from mytable where `timestamp` between '2012-05-01 00:00:00' and '2012-05-31 23:59:59') group by dayOfMonth order by dayOfMonth asc
iirc, timestamp is a reserved word in MySQL - hence it's enclosed in ticks above (note that ticks are NOT single quotes). MySQL also allows you to use column aliases in group by and order by statements. On Tue, May 8, 2012 at 3:57 AM, Che Vilnonis <ch...@asitv.com> wrote: > > Should be a simple one for a mysql guru. What is the mysql equivalent of the > following? > > select datePart(dd,timestamp) as dayOfMonth, count(timestamp) as > searchesPerDay > from mytable > where (timestamp between '5/1/2012 00:00:00' and '5/31/2012 23:59:59') > group by datePart(dd,timestamp) > order by datePart(dd,timestamp) asc > > Thanks, Che > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:351035 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
