On Thu, Aug 23, 2012 at 8:08 AM, Larry Martell <larry.mart...@gmail.com> wrote: > On Tue, Aug 21, 2012 at 8:26 PM, Larry Martell <larry.mart...@gmail.com> > wrote: >> On Tue, Aug 21, 2012 at 8:07 PM, <h...@tbbs.net> 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; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
