> I need to construct a SQL statement to get the average number of orders > per day, but I can't figure it out. > > The table name is "orderSummary" and the date field is named "ordDate."
.. Over a series of days I assume... Typically the problem here is that you have a series of dates with time values associated with them, so grouping on the date column prevents you being able to get an average per day because it's grouping per milisecond ... There are various ways to get around this -- creating a new column which contains only the date, minus the hours, minutes, seconds and miliseconds, or doing something _like_ ( MS SQL Server )... SELECT DatePart(dy,ordDate) AS dayofyear, count(orderid) FROM orderSummary WHERE orddate BETWEEN (#startdate# and #enddate#) GROUP BY orddate ORDER BY orddate This syntax may or may not work -- dates are always a bit tricky and I usually wind up creating a separate column with date-only (no time) values if I suspect I may need to group on the day. hth Isaac Dealey www.turnkey.to 954-776-0046 ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
