I just tested. You're right, DatePart works in in the group by. In that case, you don't need to create the view. In that case, something like this should work:
select Name, Email, Type, DatePosted, COUNT(*) AS num_contacts from ContactInfo_View group by DatePart(yy,DatePosted), DartPart(mm,DatePosted) , Name, Email, Type, DatePosted, At 04:09 PM 11/10/2006, you wrote: >Dunno if MSSQL is different, but I've certainly done a GROUP BY with >functions like DATEPART on other SQL engines, without needing to do >seperate selects/views or anything. > >On 11/10/06, Jeffry Houser <[EMAIL PROTECTED]> wrote: > > Grouping is the answer, but I didn't think you can group on the > > results from an aggregate function. > > > > One Solution: > > > > Create a view, using something like this: > > > > SELECT Name, Email, Type, DatePosted, DartPart(mm,DatePosted) as > > DatePostedMonth, DatePart(yy,DatePosted) as DatePostedYear > > FROM ContactInfo > > order by DatePosted > > > > Then you can select from the view: > > > > select Name, Email, Type, DatePosted, COUNT(*) AS num_contacts > > from ContactInfo_View > > group by DatePostedYear, DatePostedMonth, Name, Email, Type, DatePosted, > > > > > > There are probably others. > > > > > > At 03:40 PM 11/10/2006, you 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. > > > > > > > > -- > > Jeffry Houser, Software Developer, Writer, Songwriter, Recording Engineer > > AIM: Reboog711 | Phone: 1-203-379-0773 > > -- > > My Company: <http://www.dot-com-it.com> > > My Books: <http://www.instantcoldfusion.com> > > My Recording Studio: <http://www.fcfstudios.com> > > Connecticut Macromedia User Group: <http://www.ctmug.com> > > Now Blogging at <http://www.jeffryhouser.com> > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2614 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
