The inner query has multiple rows because of wafer_id. The outer query then gives you multiple copies, hence screwing up the COUNT.
Also, the AVG(AVG()) is mathematically incorrect unless the counts are identical. > -----Original Message----- > From: Larry Martell [mailto:larry.mart...@gmail.com] > Sent: Tuesday, August 21, 2012 7:27 PM > To: h...@tbbs.net > Cc: mysql@lists.mysql.com > Subject: Re: help with correlated subquery > > On Tue, Aug 21, 2012 at 8:07 PM, <h...@tbbs.net> wrote: > >>>>> 2012/08/21 16:35 -0600, Larry Martell >>>> > > I am trying to write a query that selects from both a correlated > > subquery and a table in the main query, and I'm having a lot of > > trouble getting the proper row count. I'm sure this is very simple, > > and I'm just missing it. I'll try and present a simple example. For > > this example, there are 27 rows, organized like this: > > > > > > mysql> select count(*), target_name_id, ep, wafer_id from data_cst > > where target_name_id = 44 group by target_name_id, ep, wafer_id; > > +----------+----------------+------+----------+ > > | count(*) | target_name_id | ep | wafer_id | > > +----------+----------------+------+----------+ > > | 6 | 44 | 1,1 | 16 | > > | 3 | 44 | 1,1 | 17 | > > | 6 | 44 | 1,2 | 16 | > > | 3 | 44 | 1,2 | 17 | > > | 6 | 44 | 1,3 | 16 | > > | 3 | 44 | 1,3 | 17 | > > +----------+----------------+------+----------+ > > 6 rows in set (0.00 sec) > > > > I need to get an average of a column grouped by target_name_id, ep as > > well as the average of the averages grouped by target_name_id, ep, > > wafer_id, and I also need the count of the rows in the > target_name_id, > > ep group. My query is getting the correct averages, but incorrect row > > counts: > > > > mysql> select count(*), target_name_id, ep, avg(bottom), > avg(averages) > > from (select avg(bottom) as averages, target_name_id as t, ep as e > > from data_cst where target_name_id = 44 group by target_name_id, ep, > > wafer_id) x, data_cst where target_name_id = t and ep = e group by > > target_name_id, ep; > > +----------+----------------+------+-------------+-----------------+ > > | count(*) | target_name_id | ep | avg(bottom) | avg(averages) | > > +----------+----------------+------+-------------+-----------------+ > > | 18 | 44 | 1,1 | 21.8056667 | 21.85458330000 | > > | 18 | 44 | 1,2 | 121.7984444 | 121.83983335000 | > > | 18 | 44 | 1,3 | 349.7634444 | 349.75016665000 | > > +----------+----------------+------+-------------+-----------------+ > > 3 rows in set (0.01 sec) > > > > The count for each row should be 9. What do I need in my count() to > be > > counting the right thing? > > <<<<<<<< > > Your trouble lys in the joining; in effect, you are joining a row > with wafer_id 16 with a row with wafer_id 17, and also a row with > wafer_id 17 with a row with wafer_id 16. > > > > A further advantage to using the now standard form of joining, as > Rick James bids you do, is that one can add further conditions to it: > > > > select count(*), target_name_id, ep, avg(bottom), avg(averages) from > > (select avg(bottom) as averages, target_name_id, ep > > from data_cst > > where target_name_id = 44 > > group by target_name_id, ep, wafer_id) x JOIN data_cst ON > > target_name_id = x.target_name_id and ep = x.ep and wafer_id < > > x.wafer_id group by target_name_id, ep > > > > The inequality, maybe, will give you what you want. > > Not, wafer_id < x.wafer_id, but wafer_id = x.wafer_id - adding that > makes it work the way I want. Thanks!! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql
