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

I got this working:

select count(distinct lot_id,data_file_id)
  from data_cst
 where target_name_id=208082
   and wafer_id=425845;
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