The problem is you are still grouping on the specific dateJoined.
Try something like
SELECT Month(DateJoined) + ' ' + Year(DateJoined) as monthJoined,
Count(*) as MonthJoinedCount
FROM Admin
GROUP BY Month(DateJoined) + ' ' + Year(DateJoined)
ORDER BY Count(*) DESC
-----Original Message-----
From: Owens, Howard [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 07, 2001 11:31 AM
To: CF-Talk
Subject: (sql) Group by Month query
I've been trying to get this group by month query to produced the desired
results. So far it's not working.
In my database, I have a field called "DateJoined" This is the actually
date somebody joined, i.e. 1/24/01, 1/22/01, 1/5/01. In other words, it's
by the day. Now I want to get a report on how many people joined in Jan.
2001, as well as June 1999, etc.
Here's the latest iteration of my query:
<cfquery name="MonthJoined"
datasource="#application.DSN#">
SELECT DateJoined, Year(Month(DateJoined)) as MonthCount,
Count(Year(Month(DateJoined))) as MonthJoinedCount
FROM Admin
GROUP BY Month(DateJoined), DateJoined
ORDER BY Count(Month(Year(DateJoined))) DESC
</cfquery>
What I get is something like (using just January as an example):
January 2001: 20
January 2001: 5
January 2001: 34
January 2001: 26
Now the total, if you ad up all of those January 2001s is correct.
But what I need is:
January 2001: 85
I can't figure out why it's not grouping all of the Jan 2001s together (same
goes for other months, other years).
BTW: I'm using an Access DB. I tried the query suggestion of at least on
person the list to use DatePart() instead of Month() and using DatePart()
always through an error. I stumbled across something on the Web where a
person was using Month() in a query, so I tried it and it didn't throw an
error.
H.
Howard Owens
Internet Operations Coordinator
www.insidevc.com
[EMAIL PROTECTED]
AIM: GoCatGo1956
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
