|
|
Posted by Bonge Boo! on 10/14/28 11:26
On 10/9/05 23:39, in article 7tn6i19etalt5k6q126i99sr7otahpdhvp@4ax.com,
"Andy Hassall" <andy@andyh.co.uk> wrote:
> 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.
Sorry, could explain what you mean by "non-aggregated columns"
>> ## 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:
I wouldn't have a clue(database design and SQL a bit new to me). Would you
mind explaining a couple of quick questions? I've flipped through and
corrected the missing , so the query runs, but its looking up my SQL query
console (phpmyadmin and CocooasSQL)...
> 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
Tried this and I get "#1030 - Got error 28 from storage engine)
> Although you haven't posted your data model so I'm just guessing.
I'm trying to visualise the table that is being created by these joins and
failing. Assume the campaign_details has 10 rows, the april_tracking has
250, and the april_hits 500 rows.
Are we producing a table that has 10x250x500 rows, then.. I just can't
visualise how the GROUP BY filters are then enabling count to work, etc.
Basically I think I'm not appreciating how the GROUP BY constraint is
working with count().
[Back to original message]
|