One thing I'll add that, at least for SQL Server 2000, when selecting aggregate functions mixed with columns, the columns must be listed in a group by.
At 03:51 PM 11/10/2006, you wrote: >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:2610 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
