See, that throws the following error as well: Column name 'ContactInfo.DatePosted' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
What gives? Where would you look next? The query certainly seems valid, and the column names are perfectly correct. -- Jeff Small LHWH Advertising Myrtle Beach, SC 29577 843-448-1123 Ext 254 ----- Original Message ----- From: Peter Boughton To: SQL Sent: Friday, November 10, 2006 3:54 PM Subject: Re: Struggling with Grouping and Count Wait, that's not entirely right. If all you want is "March 03 - 34 Submissions" etc - ie: "date - count" - then you don't need the rest; you just want this: SELECT DATEPART(mm,DatePosted), COUNT(*) AS num_contacts FROM ContactInfo GROUP BY DATEPART(mm,DatePosted) ORDER BY DatePosted On 11/10/06, Jeff Small <[EMAIL PROTECTED]> wrote: > I have a simple table. We'll call it ContactInfo. > > I want to simply group the contacts by month, and output the sum of each month. I'm using ColdFusion, so I really just want to output the following: > > March 03 - 34 Submissions > April 03 - 12 Submissions > May 03 - 8 Submissions > June 03 - 43 Submissions > > Etc > > Here's what I've got that's so far giving me fits: > > SELECT Name, Email, Type, DatePosted, COUNT(*) AS num_contacts > FROM ContactInfo > GROUP BY DATEPART(mm, DatePosted) > ORDER BY DatePosted > > And it's just not working against SQL Server 2000. I'm just all kinds of stumped. I dunno if I need to group the output, or what. > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2609 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
