MAX(columnname) as alias
along with your grouping.
dave
----- Original Message -----
From: Ben Densmore
To: CF-Talk
Sent: Tuesday, November 25, 2003 11:44 AM
Subject: RE: Group By Question
Dave,
That doesn't seem to do what I want it to. I modified the query to grab
just the fields I'm using and grouped by those fields:
<CFQUERY name="clicks_per_product_market_display" datasource="#
Data_Source #">
select
email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam
e,email_results.emres_prod_part_numl,email_results.emres_click_date
from email_results, email_blast
Where email_results.emres_blast_sent_id = #URL.BLAST# AND
email_results.emres_prod_catagory = '#get_cat_info.Category#' AND
email_blas_id = emres_blast_email_id
Group By
email_blast.embl_email,email_blast.embl_companyname,email_blast.embl_nam
e,email_results.emres_prod_part_numl,email_results.emres_click_date
Order By email_blast.embl_companyname
</CFQUERY>
I still get duplicate data so if person from companyA clicked twice on a
product the query will show
companyA [EMAIL PROTECTED]
companyA [EMAIL PROTECTED]
I want companyA to show up only once even if they clicked 5 times. Does
that make sense?
Ben
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 25, 2003 11:43 AM
To: CF-Talk
Subject: RE: Group By Question
> I have a query that currently looks like:
>
> <CFQUERY name="clicks_per_product_market_display" datasource="#
> Data_Source #">
> select *
> from email_results, email_blast
> Where email_results.emres_blast_sent_id = #URL.BLAST# AND
> email_results.emres_prod_catagory = '#get_cat_info.Category#' AND
> email_blas_id = emres_blast_email_id
> Group By email_blast.embl_email
> ORDER BY email_blast.embl_companyname
> </CFQUERY>
>
> I always get an error that the other fields aren't contained in an
> aggregate function. How can I get this query to Group By just the
> embl_email field? I used the group property in my cfoutput which
> does group all the emails but I need to get a count on the unique
> emails that clicked through and not every email.
Just list the other selected fields after email_blast.embl_email. You
should
get the aggregation you want that way.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
