The group by part must be enough to distinguish unique records - if you've got multiple names/types/emails in the same month, then you need to include enough information to make it unique.
The easiest way to ensure that is to group by everything selected, except the aggregate function (in this case COUNT, but also applies to SUM, AVG, etc). If your selection includes the primary key, you may be able to get away with just using that - so perhaps in this case you just need dateposted and email, if those are enough to make things unique. Also, your datepart(mm,...) bit needs to appear in the SELECT clause as well as the GROUP BY. So, in summary, try this: SELECT Name, Email, Type, DATEPART(mm,DatePosted), COUNT(*) AS num_contacts FROM ContactInfo GROUP BY Email, DATEPART(mm, DatePosted) ORDER BY DatePosted And if that doesn't work, try this: SELECT Name, Email, Type, DATEPART(mm, DatePosted), COUNT(*) AS num_contacts FROM ContactInfo GROUP BY Name, Email, Type, DATEPART(mm, DatePosted) ORDER BY DatePosted Hope that all makes sense? -- Peter 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:2605 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
