List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:August 22 2012 1:56am
Subject:Re: help with correlated subquery
View as plain text  
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
>>
Thread
help with correlated subqueryLarry Martell21 Aug
  • RE: help with correlated subqueryRick James21 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
  • Re: help with correlated subqueryhsv22 Aug
    • Re: help with correlated subqueryLarry Martell22 Aug
      • RE: help with correlated subqueryRick James22 Aug
      • Re: help with correlated subqueryLarry Martell23 Aug
        • Re: help with correlated subqueryLarry Martell23 Aug
Re: help with correlated subqueryLarry Martell22 Aug
RE: help with correlated subqueryMartin Gainty22 Aug
  • Re: help with correlated subqueryShawn Green22 Aug
    • Re: JOIN, JOIN, JOINhsv23 Aug