On Tue, Aug 21, 2012 at 5:39 PM, Martin Gainty <mgai...@hotmail.com> wrote: > a look at the first query: > > 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 | > > a look at the second query which references the 1st query > > 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 nt er_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 | > > you have 3 rows returned based on wafer_id > wafer_id = 16 returns count of 6 > count of 6 > count of 6 > count(*) = 18 > > but you dont want the rows aggregated by wafer_id (or any other criteria) > you want your rows aggregated by only column ep > (1,1 with 1,1) ROW 1 and ROW 2 c > (1,2 with 1,2) ROW3 and ROW 4 > (1,3 with 1,3) ROW5 and ROW6 > so i would group ONLY on ep
No, I need to aggregate on target_name_id and ep. In the real app there are multiple target_name_ids being selected. I was trying to present a simple example. > > Its a tough call without seeing each query executed individually > what I do is build out the query column at a time > and then I add in group by > then I add in aggregate functions > avg(bottom) > avg(averages) > > that way I can see each variable being used and which one delivers correct > result (and which variable goes fubar) I've done that. The inner query gives the expected results: mysql> select target_name_id as t, ep as e, avg(bottom) as averages from data_cst where target_name_id = 44 group by target_name_id, ep, wafer_id; +----+------+-------------+ | t | e | averages | +----+------+-------------+ | 44 | 1,1 | 21.7078333 | | 44 | 1,1 | 22.0013333 | | 44 | 1,2 | 121.7156667 | | 44 | 1,2 | 121.9640000 | | 44 | 1,3 | 349.7900000 | | 44 | 1,3 | 349.7103333 | +----+------+-------------+ 6 rows in set (0.00 sec) As does the outer one: mysql> select count(*), target_name_id as target, ep as ep, avg(bottom) from data_cst where target_name_id = 44 group by target_name_id, ep; +----------+--------+------+-------------+ | count(*) | target | ep | avg(bottom) | +----------+--------+------+-------------+ | 9 | 44 | 1,1 | 21.8056667 | | 9 | 44 | 1,2 | 121.7984444 | | 9 | 44 | 1,3 | 349.7634444 | +----------+--------+------+-------------+ 3 rows in set (0.00 sec) It only when I combine them that I am double counting the target, ep rows. I think I see why (there are 2 wafers for each target, ep), but I don't know how to avoid that. I need to group by target_name_id, ep, wafer_id in the inner query, and then I need to group by target_name_id, ep in the outer one. I only want to count the number of target_name_id, ep groups. >> Date: Tue, 21 Aug 2012 16:35:23 -0600 > >> Subject: help with correlated subquery >> From: larry.mart...@gmail.com >> To: mysql@lists.mysql.com > >> >> 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? >> >> >> TIA! >> -larry >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
