On Tue, Aug 21, 2012 at 5:30 PM, Rick James <rja...@yahoo-inc.com> wrote:
> 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;
>
> Please qualify all fields with table names (or aliases). I can't tell what
> ep and e are. Etc.
>
> Please turn the "commajoin" into an explicit JOIN...ON.
select count(*), target_name_id as target, ep as 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
join (data_cst)
on data_cst.target_name_id = x.t and ep = x.e
group by target_name_id, ep;
Returns the same result set.
>
>> -----Original Message-----
>> From: Larry Martell [mailto:larry.mart...@gmail.com]
>> Sent: Tuesday, August 21, 2012 3:35 PM
>> To: mysql mailing list
>> Subject: help with correlated subquery
>>
>> 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
