Ooops. When I said "...around the group by as well.", that was of course a type for "around the _order_ by as well".
On 11/10/06, Peter Boughton <[EMAIL PROTECTED]> wrote: > Hmmm, maybe you need the datepart function around the group by as well. > > Or, if its not that, maybe this... > SELECT DATEPART(mm,DatePosted) as month_year, COUNT(*) AS num_contacts > FROM ContactInfo > GROUP BY DATEPART(mm,DatePosted) > ORDER BY month_year > > > On 11/10/06, Jeff Small <[EMAIL PROTECTED]> wrote: > > 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:2615 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
