Hi Mike, This is a good suggestion. We ended up changing the requirements to not require the functionality I was trying to develop at the time. However, I did just change a temporary table I'm using for a similar process to HEAP and saw a very nice perfomance improvement. Should have thought about that before.
Thanks, John A. McCaskey -----Original Message----- From: Michael McTernan [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 11:16 AM To: John McCaskey Cc: [EMAIL PROTECTED] Subject: RE: GROUP BY across UNION 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]
