On Tue, Aug 21, 2012 at 5:39 PM, Martin Gainty <mgainty@stripped> 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.martell@stripped
>> To: mysql@stripped
>
>>
>> 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
>>
