Posted by Andy Hassall on 10/01/96 11:26

On Sat, 10 Sep 2005 11:46:46 +0100, Bonge Boo! <bingbong@spamcop.net> wrote:

>On 10/9/05 10:43, in article BF48696D.AD215%bingbong@spamcop.net, "Bonge
>Boo!" <bingbong@spamcop.net> wrote:
>
>CREATE TEMPORARY TABLE IF NOT EXISTS summarised
>
>SELECT
>campaign_details.datasource,
>campaign_details.supplier AS Supplier,
>campaign_details.type AS 'Type',
>campaign_details.datasource_name AS 'Campaign Name',
>campaign_details.link AS 'Link',
>campaign_details.campaign AS 'Campaign',
>count(april_tracking.datasource) AS 'Unique Vistors'
>FROM campaign_details LEFT JOIN april_tracking USING (datasource)
>WHERE campaign_details.campaign = 'april05'
>GROUP BY campaign_details.datasource;

GROUP BY should contain all the non-aggregated columns, else the results are
potentially unpredictable. MySQL is one of the very few databases that doesn't
enforce this.

>## Join our second query to results gather from the first table
>SELECT
>summarised.*,
>count(april_hits.datasource) AS 'Total Visits'
>FROM summarised LEFT JOIN april_hits USING (datasource)
>GROUP BY summarised.datasource;

Isn't this the same as just:

SELECT campaign_details.datasource,
campaign_details.supplier AS Supplier,
campaign_details.type AS 'Type',
campaign_details.datasource_name AS 'Campaign Name',
campaign_details.link AS 'Link',
campaign_details.campaign AS 'Campaign',
count(DISTINCT april_tracking.datasource) AS 'Unique Vistors'
count(april_hits.datasource) AS 'Total Visits'
FROM campaign_details
LEFT JOIN april_tracking USING (datasource)
LEFT JOIN april_hits USING (datasource)
WHERE campaign_details.campaign = 'april05'
GROUP BY campaign_details.datasource,
campaign_details.supplier
campaign_details.type,
campaign_details.datasource_name,
campaign_details.link,
campaign_details.campaign

Although you haven't posted your data model so I'm just guessing.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

• Next in forum: Re: Order By - problem
• Prev in forum: Re: Mysql_query() only allows one SQL query at a time. Other options?
• Thread view: Re: SQL Sub-query syntax question..

[Reply to this message]