Thank you very much for the code, Glenn, and the thought that went behind it.
Unfortunately, the SUM approach is returning the exact same multiplier effect
as COUNT was. Looks like I will have to break this out into individual
queries. At least I learned something from your code (I had never used SUM
before in the way you have below). Thanks!
Kamie
Glenn Olsen wrote:
> Kamie,
>
> The results you are getting make perfect sense. The LEFT JOINS return NULL
> values where there is no match in the right-side table. COUNT is treating
> NULL as a value and incrementing your output column for each and every row.
> Instead of using COUNT, redo this using SUM with CASE statements, as below.
> (I haven't actually run this, so let me know if it's not exactly right.)
>
> SELECT
> c.company,
> SUM(CASE WHEN srl.company_id IS NOT NULL THEN 1
> ELSE 0
> END) AS search_count,
> SUM(CASE WHEN el.company_id IS NOT NULL THEN 1
> ELSE 0
> END) AS enhanced_count,
> SUM(CASE WHEN ctl.company_id IS NOT NULL THEN 1
> ELSE 0
> END) AS click_through_count,
> SUM(CASE WHEN cfl.company_id IS NOT NULL THEN 1
> ELSE 0
> END) AS contact_me_count
> FROM dbo.Companies c
> LEFT OUTER JOIN dbo.Search_Results_Log srl ON
> c.company_id=srl.company_id
> LEFT OUTER JOIN dbo.Enhanced_Log el ON c.company_id=el.company_id
> LEFT OUTER JOIN dbo.Click_Through_Log ctl ON
> c.company_id=ctl.company_id
> LEFT OUTER JOIN dbo.Contact_Form_Log cfl ON
> c.company_id=cfl.company_id
> WHERE c.company_id= "#attributes.report_company#"
> GROUP BY company
>
> -----Original Message-----
> From: Kamie Curfman [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, September 13, 2001 5:15 PM
> To: CF-Talk
> Subject: Re: Left Outer Join Question
>
> The LEFT JOIN didn't change anything. Thanks for the help, though. And
> thanks to cfrefactoring. I was hoping to not have to break it up into
> individual queries, but if that's what I have to do, that's what I have to
> do. Thank you!
>
> Kamie
>
> Mark Stewart wrote:
>
> > Have you tried just a LEFT JOIN?
> >
> > -----Original Message-----
> > From: Kamie Curfman [mailto:[EMAIL PROTECTED]]
> > Sent: Thursday, September 13, 2001 2:19 PM
> > To: CF-Talk
> > Subject: Left Outer Join Question
> >
> > I know this is more SQL than CF, but this list is so responsive...
> >
> > I'm trying to do a query that counts how many times a particular company
> > showed up in the search results log table, how many times its URL was
> > clicked on, etc. So, I'm left outer joining all the log tables, on the
> > chance that the company wasn't returned in any of the searches, its URL
> > was never clicked on, etc. I thought the below logic was sound, but
> > instead of getting "14" for search_count, "4" for enhanced_count, "1"
> > for click_through_count, and "1" for contact_me_count, I'm getting "56"
> > for all. In other words, 14*4*1*1. Why is this happening? Why aren't
> > I getting separate counts for each?
> >
> > Here is the query. Thank you so much in advance.
> >
> > <cfquery datasource="#request.datasource#" name="get_company">
> > SELECT c.company, COUNT(srl.company_id) AS search_count,
> > COUNT(el.company_id) AS enhanced_count, COUNT(ctl.company_id) AS
> > click_through_count, COUNT(cfl.company_id) AS contact_me_count
> > FROM dbo.Companies c
> > LEFT OUTER JOIN dbo.Search_Results_Log srl ON
> > (c.company_id=srl.company_id)
> > LEFT OUTER JOIN dbo.Enhanced_Log el ON
> > (c.company_id=el.company_id)
> > LEFT OUTER JOIN dbo.Click_Through_Log ctl ON
> > (c.company_id=ctl.company_id)
> > LEFT OUTER JOIN dbo.Contact_Form_Log cfl ON
> > (c.company_id=cfl.company_id)
> > WHERE c.company_id=<cfqueryparam
> > value="#attributes.report_company#"
> > cfsqltype="cf_sql_integer">
> > GROUP BY company
> > </cfquery>
> >
> > Kamie
> >
> > --
> > Kamie B. Curfman
> > Matrix Group International, Inc.
> > 801 N. Fairfax Street, Suite 205
> > Alexandria, VA 22314
> > Phone: (703) 838-9777 x3014
> > Fax: (703) 838-9799
> > [EMAIL PROTECTED]
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
