I agree with Mike.
In order to use the aggregate functions across both tables' worth of data
you will have to combine them into one large table. That can be a real
table or a temporary one but in either case you are stuck combining them
BEFORE the calculations or MySQL won't be able to crunch the numbers
correctly.
One way not mentioned yet could be to use a derived table ( a temp table
built into the query):
SELECT id_field, AVG(avg), STD(avg), Min(avg)
FROM ((SELECT id_field, avg from table_a) union (SELECT id_field, avg from
table_b))
GROUP by id_field
If MySQL had FULL OUTER JOIN, you could use that too but that feature is
not released yet.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Michael McTernan"
<Michael.McTernan@ To: "John McCaskey" <[EMAIL
PROTECTED]>
ttpcom.com> cc: <[EMAIL PROTECTED]>
Fax to:
06/18/2004 02:15 Subject: RE: GROUP BY across UNION
PM
Hi John,
Depending on the size of your datasets, you could merge the data into a
TEMPORARY table and then compute from there? If the temp table is small
enough it will fit in RAM as a heap table, and will probably be more
efficient than fetching all the results and computing them in code. Of
course, if the dataset is large enough, the temporary table will hit the
disc, and then it will be very inefficent though.
Thanks,
Mike
> -----Original Message-----
> From: John McCaskey [mailto:[EMAIL PROTECTED]
> Sent: 24 February 2004 17:08
> To: [EMAIL PROTECTED]
> Subject: RE: GROUP BY across UNION
>
>
> Nope, this would yield a 'Column: 'avg' in field list is ambiguous'
> error. I'm still trying to work out a better way of doing this. I also
> need to get standard deviations now, and the method I mentioned in my
> original post doesn't even work for that. So now I'm left with actually
> getting all the values and computing them in code. Very sub optimal.
>
> John A. McCaskey
>
>
> -----Original Message-----
> From: Ligaya Turmelle [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 18, 2004 6:30 PM
> To: [EMAIL PROTECTED]
> Subject: Re: GROUP BY across UNION
>
>
> I am pretty new myself but can't you do it with a join? Like SELECT
> AVG(avg) FROM table_a, table_b GROUP BY id_field.
>
> Respectfully,
> Ligaya Turmelle
>
> ""John McCaskey"" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> First of all I am using mysql 4.0.18 and I am not free to upgrade to 4.1
> or 5.0.
>
>
>
> I have two tables:
>
>
>
> table_a, and table_b these two tables have the same structure:
>
> CREATE table_a (
>
> id_field mediumint(8) unsigned NOT NULL,
>
> avg float default NULL
>
> )
>
>
>
> What I want to do is get the combined avg across table_a and table_b for
> each id value. So I tried doing
>
> (SELECT AVG(avg) FROM table_a)
>
> UNION
>
> (SELECT AVG(avg) FROM table_b)
>
> GROUP BY id_field;
>
>
>
> This however doesn't work. I can see why this is thinking about how a
> union should work mathematically but I'm left with no apparent way to
> directly get the avg across two tables. I know that I can take the avg
> from each along with the row count and then do a weighted average using
> those values, but I'd prefer not to have to do that. Any suggestions or
> am I stuck doing individual group bys on each table uninoning the
> results and then doing weighted averages on the matching pairs?
>
>
>
> Here is what I'm talking about doing with the weighted averages incase
> it is unclear:
>
> (SELECT AVG(avg) AS avg_a, COUNT(id_field) AS count_a FROM table_a GROUP
> BY id_field)
>
> UNION
>
> (SELECT AVG(avg) AS avg_b, COUNT(id_field) AS count_b FROM table_b GROUP
> BY id_field);
>
>
>
> Then I would match up the results and compute total_avg = (avg_a*count_a
> + avg_b*count_b)/(count_a+count_b). This is not nearly as clean as I
> would like.
>
>
>
>
>
>
>
> John A. McCaskey
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
