>- see footer for list info -< Well if that works, try: <CFQUERY NAME="GetRequests2" DATASOURCE="#request.maindsn#"> SELECT day(R.RequestDate) as rd, Count(R.RequestID) AS Total FROM Requests R WHERE R.RequestDate BETWEEN #attributes.fromdate# AND #attributes.todate# GROUP BY MONTH(R.RequestDate), day(R.RequestDate) </CFQUERY>
or swap the group by around. You might want YEAR() in there as well. Ade -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Allan Cliff Sent: 14 June 2005 14:01 To: CF Developer Subject: [CF-Dev] Grouping by date in SQL >- see footer for list info -< I want to group a table by date. The date column is a date/time column but I want to group on a per day basis. i.e. <CFQUERY NAME="GetRequests2" DATASOURCE="#request.maindsn#"> SELECT day(R.RequestDate) as rd, Count(R.RequestID) AS Total FROM Requests R WHERE R.RequestDate BETWEEN #attributes.fromdate# AND #attributes.todate# GROUP BY day(R.RequestDate) </CFQUERY> This works fine but when the date span is over a month then the 1st June is obvioulsy treated as 1 as is the first of any month I want to group by the date irrespective of the time. Hope you get it. Allan _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.1 - Release Date: 13/06/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.1 - Release Date: 13/06/2005 _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
