PHP SQL - SQL Sub-query syntax question..

Author

SQL Sub-query syntax question..

Bonge Boo!

2005-09-09, 7:55 am

I'm not sure if I'm going about this the sensible way for not, so...

I have 2 tables. Once contains details of various events. Name of the event,
when it starts, how many days and a primary key.

Another table has the names of people going to the events, and a key that
corresponds to an event in the first table.

I want to write a query that will produce a recordset that has the name of
the event and the number of people attending it.

So basically name of event, then count(query to find people attending event
x)

I could do this in PHP by created a nested sql query, then use array push to
add that data into a bigger array, then output that. Which is what I've
done. But I was hoping to be able to write an SQL query so I could then use
the ADODB function to output to a CSV file.

Is it possible to write an SQL query to do what I've tried to explain above,
or should I just stick with PHP and write the functions to export as CSV.

Any suggestions? My MYSQL query skills are limited, but I'm trying to expand
them. This seemed a good place to start.

Hilarion

2005-09-09, 7:55 am

> I'm not sure if I'm going about this the sensible way for not, so...
>
> I have 2 tables. Once contains details of various events. Name of the event,
> when it starts, how many days and a primary key.
>
> Another table has the names of people going to the events, and a key that
> corresponds to an event in the first table.
>
> I want to write a query that will produce a recordset that has the name of
> the event and the number of people attending it.
>
> So basically name of event, then count(query to find people attending event
> x)
>
> I could do this in PHP by created a nested sql query, then use array push to
> add that data into a bigger array, then output that. Which is what I've
> done. But I was hoping to be able to write an SQL query so I could then use
> the ADODB function to output to a CSV file.
>
> Is it possible to write an SQL query to do what I've tried to explain above,
> or should I just stick with PHP and write the functions to export as CSV.
>
> Any suggestions? My MYSQL query skills are limited, but I'm trying to expand
> them. This seemed a good place to start.

To get count of people for each event do something like this:

SELECT event_table.event_id, event_table.event_name, count( people_table.person_id ) AS cnt
FROM event_table LEFT JOIN people_table ON event_table.event_id = people_table.event_id
GROUP BY event_table.event_id, event_table.event_name
ORDER BY event_table.event_name, event_table.event_id

To get a count of people for one event you could add WHERE clause to the query
above (after FROM clause and before GROUP BY clause) or use one query to
get the event data and another to get the count:

SELECT count(*) AS cnt
FROM people_table
WHERE event_id = $event_id

If you want to use the first query and get more data from the event_table, then
don't forget to put the additional column names in GROUP BY clause (it's probably
not needed in MySQL, but will make the query work also in other SQL engines
and should be less dependant on the table structure).

Hilarion

Bonge Boo!

2005-09-09, 6:57 pm

On 9/9/05 13:29, in article dfrv2r$90m$1@news.onet.pl, "Hilarion"
<hilarion@SPAM.op.SMIECI.pl> wrote:

>
>
> To get count of people for each event do something like this:
>
> SELECT event_table.event_id, event_table.event_name, count(
> people_table.person_id ) AS cnt
> FROM event_table LEFT JOIN people_table ON event_table.event_id =
> people_table.event_id
> GROUP BY event_table.event_id, event_table.event_name
> ORDER BY event_table.event_name, event_table.event_id
>
> To get a count of people for one event you could add WHERE clause to the query
> above (after FROM clause and before GROUP BY clause) or use one query to
> get the event data and another to get the count:
>
> SELECT count(*) AS cnt
> FROM people_table
> WHERE event_id = $event_id
>
>
> If you want to use the first query and get more data from the event_table,
> then
> don't forget to put the additional column names in GROUP BY clause (it's
> probably
> not needed in MySQL, but will make the query work also in other SQL engines
> and should be less dependant on the table structure).

<Gulp> Lots to digest and learn </Gulp>

I didn't realise I could count the hits from a column like that. Very
useful. I'll now go play with it and see if I can extend it to further
tables.

Thank you.

Bonge Boo!

2005-09-10, 6:56 pm

On 9/9/05 13:29, in article dfrv2r$90m$1@news.onet.pl, "Hilarion"
<hilarion@SPAM.op.SMIECI.pl> wrote:

> If you want to use the first query and get more data from the event_table,
> then
> don't forget to put the additional column names in GROUP BY clause (it's
> probably
> not needed in MySQL, but will make the query work also in other SQL engines
> and should be less dependant on the table structure).

Thanks for the code. Works like a charm. I also have a another table which
has enquiries about the events, again with the event_id as the foreign key.

What I was going to do was have the event, the number of enquiries and the
number of people who actually booked returned in a result.

To do this I'd need to join 3 tables, and also count from 2 different
tables. I don't think I can do this using the code example you've given me?
I try to extend the join and it messes up. The count() thing has me a little

. I'm choosing to count the number of unique results in the column
specified, and the GROUP BY statement "collapses" the table returned so we
only see rows for the "unique" values found I nthe GROUP By column?

Which would mean trying to add a 2nd count and group by would mess things up
completely?

So I thought the obvious thing to do would be to have 2 SQL queries, and
then perform a join on the tables they produce. I guessd I would be able to
temporarily "name" the result retrieved from the each query, then use those
names that to join the 2 results together. Google doesn't seem to bring up
anything relevant, nor does scanning the MYSQL manual.

The only thing I can found like this is to store the results of the first
query in a temporary table and then join that to to the 2nd query.

Is that sensible or am I missing a trick?

Bonge Boo!

2005-09-10, 6:56 pm

On 10/9/05 10:43, in article BF48696D.AD215%bingbong@spamcop.net, "Bonge
Boo!" <bingbong@spamcop.net> wrote:

> The only thing I can found like this is to store the results of the first
> query in a temporary table and then join that to to the 2nd query.
>
> Is that sensible or am I missing a trick?

I hope I can answer this question myself now. Assuming I'm not missing
another obvious way of doing it. My SQL is below

## make a temporary table to store the results of the first query in
## Note!! Don't put a ; on the the end of the CREATE TEMPORARY TABLE query
## You make a blank table!
##This method copies the result of the SELECT into the temp table

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;

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

Hope that might be vaguely of interest to someone out there, and many thanks
to Hilarion!

Andy Hassall

2005-09-10, 6:56 pm

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

Bonge Boo!

2005-09-11, 7:55 am

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"

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

Andy Hassall

2005-09-11, 6:56 pm

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:
>
>
>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.

>
>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)...
>
>
>Tried this and I get "#1030 - Got error 28 from storage engine)

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

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

Sponsored Links