Re: Getting a count from a group in cfquery

You could try count(distinct emailID). On Wed, May 6, 2009 at 8:42 AM, Jason Fisher wrote: > > Try a sub-query.  Pretty sure this will get you what you're looking for (or > close): > > SELECT sub.thisCount, >        sub.emailID, >        e.emailAddress >        sub.emailDate, >        sub.group

Re: Getting a count from a group in cfquery

Try a sub-query. Pretty sure this will get you what you're looking for (or close): SELECT sub.thisCount, sub.emailID, e.emailAddress sub.emailDate, sub.groupID FROM log_email e RIGHT OUTER JOIN ( SELECT COUNT(emailAddress) AS thisCount,

Re: Getting a count from a group in cfquery

you can also do it cf-only way: SELECT emailID, emailADDRESS, emailDATE, groupID FROM log_email WHERE emailID = ORDER BY emailDATE DESC #thisCOUNT# emails sent on #thisLOG.emailDATE# #thisLOG.emailADDRESS# not as efficient as db way, but works too

Re: Getting a count from a group in cfquery

gt;> From: Les Mizzell [mailto:lesm...@bellsouth.net] >> Sent: 05 May 2009 16:56 >> To: cf-talk >> Subject: Getting a count from a group in cfquery >> >> >> What I'm trying to get: >> >> >>#thisCOUNT# emails sent on #thisLOG.emailDATE

RE: Getting a count from a group in cfquery

You need to include the other columns in your SELECT in your GROUP BY clause. Adrian > -Original Message- > From: Les Mizzell [mailto:lesm...@bellsouth.net] > Sent: 05 May 2009 16:56 > To: cf-talk > Subject: Getting a count from a group in cfquery > > >

Re: Getting a count from a group in cfquery

probably a couple ways to do this.. in Oracle I'd use the over partition by analytic function. SELECT COUNT(groupID) over (partition by groupID) as thisCOUNT, emailID, emailADDRESS, emailDATE, groupID FROM log_email WHERE emailID = ORDER BY emailDATE desc then you dont need the

Getting a count from a group in cfquery

What I'm trying to get: #thisCOUNT# emails sent on #thisLOG.emailDATE# #thisLOG.emailADDRESS# "thisCOUNT" above would be the number of emails in a particular group Here's what I want to do in my query - but errors work since everything isn't included in a scalar function.