List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:August 23 2012 2:08pm
Subject:Re: help with correlated subquery
View as plain text  
On Tue, Aug 21, 2012 at 8:26 PM, Larry Martell <larry.martell@stripped> wrote:
> On Tue, Aug 21, 2012 at 8:07 PM,  <hsv@stripped> 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!!

So now that I have this working, that have added another requirement.
They also want a count of rows aggregated by a different set of
columns. So I need to add another subquery, but I can't figure out how
to get the row count. In this example query:

mysql> select count(*) from data_cst where target_name_id=208082 and
wafer_id=425845 group by target_name_id,wafer_id,lot_id,data_file_id;
+----------+
| count(*) |
+----------+
|       12 |
|       12 |
|       12 |
|       12 |
+----------+
4 rows in set (0.00 sec)


The result I need is 4 (i.e there were 4 distinct groups of
target_name_id,wafer_id,lot_id,data_file_id). How can I get that?

This will be a subquery, so I can't use mysql_num_rows() or
FOUND_ROWS() after the fact. I need the result returned from the
query.
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