freelanceprogrammers.org Forum Index » Cold Fusion
simple group by month question, I hope...
Joined: 18 Aug 2005
Posts: 2
simple group by month question, I hope...
This is my query for grouping sales by day over the last 30 days. Any ideas how I can perform the same grouping except show the month totals for the past 12 months instead of the daily totals?
<CFSET todaysdate = Now()>
<cfquery name="daily" datasource="lilleys" maxrows="30">SELECT SUM(subtotal) as DailyTotal, jobdate FROM jobs where jobdate < <cfoutput>#todaysdate#</cfoutput> and status = `X` GROUP BY jobdate order by jobdate DESC;</cfquery>
Thanks.
Dan
Joined: 12 Jul 2005
Posts: 20
simple group by month question, I hope...
> This is my query for grouping sales by day over the last 30 days.
> Any ideas how I can perform the same grouping except show the
> month totals for the past 12 months instead of the daily totals?
The syntax will depend on which database you`re using, as date functions
vary a lot in different varieties of SQL. This is based on the syntax
for Oracle, since that`s pretty readable (I won`t be testing it).
SELECT SUM(subtotal) as MonthlyTotal,
to_char(jobdate, `YYYYMM`) as yearandmonth
FROM jobs
where jobdate < <cfoutput>#todaysdate#</cfoutput>
and status = `X`
GROUP BY yearandmonth
order by yearandmonth DESC
I assume that any DB has some sort of function that will have that sort
of effect. You might also want to select something like
"to_char(jobdate, `MON YYYY`) as readabledate", and add readabledate to
your GROUP, as most humans find "200508" a bit unfriendly, and would
prefer to see "AUG 2005". Use yearandmonth to sort by, though.
BTW, watch out for this:
<CFSET todaysdate = Now()>
where jobdate < <cfoutput>#todaysdate#</cfoutput>
The variable todaysdate is being passed into the query as a string, and
silently converted back to a date by the SQL engine. It`s only by chance
that you`re getting it in a format that the "convert back" section
recognises. At the moment, presumably it works, but if you ever swap to
a different DB (or a different ODBC driver?) or have the default date
format change under your feet, you`re letting yourself in for trouble.
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
Booking Calendar - reservation calendar script
Land Surveying - total station instruments and equipments
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
Booking Calendar - reservation calendar script
Land Surveying - total station instruments and equipments
China Wholesale - Electronics Products
Character Studio - Tutorials and Help