It sounds like you are looping over the 'years' and running the query multiple times. Add the year as part of the query and you can do it all in one query. You can get the year using datepart.
This is how I set it up in previous e-mails. At 05:05 PM 11/10/2006, you wrote: >This one worked like a dream. I actually just sat and stared at it >for a couple minutes, and it makes total sense now. > >The ONLY thing I needed was a where clause to break it up by year >(2004, 2005, 2006, etc). I just made that part dynamic. > >That was sweet. Thanks for the help. > > >-- >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 4:13 PM > Subject: Re: Struggling with Grouping and Count > > > 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:2617 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
