SELECT COUNT(DISTINCT emres_prod_part_numl+ emres_prod_catagory) As
Clicks,emres_prod_part_numl, emres_prod_catagory
if emres_prod_part_numl and emres_prod_catagory are of different datatypes,
then you have to convert them into a single datatype using the CONVERT
function as in SQL Server.
eg: SELECT COUNT(DISTINCT convert (varchar,emres_prod_part_numl) +
emres_prod_catagory) As Clicks,emres_prod_part_numl, emres_prod_catagory
Ofcourse, here I am assuming that emres_prod_catagory is of type varchar and
emres_prod_part_numl is not a varchar. If both are varchar, then u dont have
to use the CONVERT function.
Thanks,
Milan
>From: "Ben Densmore" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: CF-Talk <[EMAIL PROTECTED]>
>Subject: Another Query Question
>Date: Wed, 26 Nov 2003 10:52:39 -0500
>
>I have a query that currently looks like:
>
> <CFQUERY name="clicks_per_product" datasource="#Data_Source#">
> select Count(*) As Clicks,emres_prod_part_numl,
>emres_prod_catagory
> from email_results
> Where emres_blast_sent_id = 23
> GROUP BY emres_prod_part_numl, emres_prod_catagory
> </CFQUERY>
>
>Based on the current emres_blast_sent_id I get output that looks like
>this:
>
>Part Number Category Click Thrus
>
>HMCXXXXX Amplifiers 389
>HMCXXXXX Modulators - Direct 287
>HMCXXXXX Modulators - Direct 132
>HMCXXXXX Dividers & Detectors 125
>
>
>This is all fine except the click thrus are off because of duplicate
>entries. How can I get a distinct count on these? Using Count(*) is
>counting every click thru and I only need it to count unique ones.
>
>Thanks,
>Ben
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
