Posted by Andy Hassall on 10/14/00 11:26

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

>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"

The columns in the GROUP BY clause, i.e. those that don't have aggregate
functions like COUNT, SUM, AVG, MIN, MAX around them.

>>> ## 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)

That's "disk full" apparently, according to Google.

>> 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().

GROUP BY basically takes sets of rows from the data queried, and aggregates
them down to one row. The COUNT function works on the original un-aggregated
data for that group - so if it reduced 10 rows to 1, you get a COUNT of 10.

SELECT campaign_details.datasource,
count(DISTINCT april_tracking.datasource),
count(april_hits.datasource)
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,

Again, I don't know your datamodel so some of this is guesswork. In particular
I don't know the meaning or how unique the identically named "datasource"
column is in each of the tables.

Presumably you're filtering to one row in campaign_details with the WHERE
clause.

You're then joining to multiple rows in april_tracking, each one of which
represents a unique person? So the GROUP BY will produce one row for the single
row from campaign_details, then count the number of people.

Then joining to multiple rows in the april_hits table; the COUNT will count
how many of those there are, but that could multiply up the previous COUNT on
april_tracking, which is why I put the DISTINCT there, but it's not possible to
tell whether that does what you want unless you post more details on your data
model.

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

[Back to original message]