Re: help with correlated subquery

2012-08-23 Thread Larry Martell 
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.8545833 |
 |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
 |   18 | 44 | 1,3  | 349.763 | 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.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: help with correlated subquery

2012-08-23 Thread Larry Martell 
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.8545833 |
 |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
 |   18 | 44 | 1,3  | 349.763 | 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

RE: help with correlated subquery

2012-08-22 Thread Martin Gainty 

assign realistic alias names
OuterJoin should be called OuterJoin
InnerJoin should be called InnerJoin

If you want only the most restricitive criteria that match resultsets from both 
select statements use INNER JOIN
if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN

Finally: Be aware FUNCTIONS such as AVG cast off indexing and should be avoided 
unless the FUNCTION(columnName) itself is indexed
GROUP BY re-arranges your query so its best to introduce GROUP BY in stages

use realistic alias names like Dept and EmployeeNumber and avoid aliases that 
cause confusion like 'a' or 'foo'

Develop in stages and write down what YOU EXPECT vs WHAT each query produces ..
If the Individual Select doesnt produce expected results..STOP and correct the 
SELECT Statement

Diagram out what you expect results from any of the INNER JOIN, OUTER JOIN, 
LEFT JOIN or RIGHT JOIN operations will produce
If the executed JOIN Statement does not produce expected results STOP and 
correct the JOIN clause BEFORE incorporating more functionality

Obfuscation and confusion can hopelessly sidetrack any intelligent analysis
Martin 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.


 Date: Tue, 21 Aug 2012 20:26:51 -0600
 Subject: Re: help with correlated subquery
 From: larry.mart...@gmail.com
 To: h...@tbbs.net
 CC: mysql@lists.mysql.com
 
 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.8545833 |
  |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
  |   18 | 44 | 1,3  | 349.763 | 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

RE: help with correlated subquery

2012-08-22 Thread Rick James 
The inner query has multiple rows because of wafer_id.  The outer query then 
gives you multiple copies, hence screwing up the COUNT.

Also, the AVG(AVG()) is mathematically incorrect unless the counts are 
identical.

 -Original Message-
 From: Larry Martell [mailto:larry.mart...@gmail.com]
 Sent: Tuesday, August 21, 2012 7:27 PM
 To: h...@tbbs.net
 Cc: mysql@lists.mysql.com
 Subject: Re: help with correlated subquery
 
 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.8545833 |
  |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
  |   18 | 44 | 1,3  | 349.763 | 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!!
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: help with correlated subquery

2012-08-22 Thread Shawn Green 

Hello Martin,

On 8/22/2012 8:30 AM, Martin Gainty wrote:


assign realistic alias names
OuterJoin should be called OuterJoin
InnerJoin should be called InnerJoin



Almost!  MySQL does not have a simple OUTER JOIN command (some RDBMSes 
call this a FULL OUTER JOIN). What we do have is the option to include 
the OUTER keyword into our LEFT or RIGHT joins. For example, both of 
these are acceptable:


LEFT OUTER JOIN
LEFT JOIN

Also, you need a space between inner and join as in INNER JOIN.


If you want only the most restricitive criteria that match resultsets from both 
select statements use INNER JOIN
if you want all results from both resultsets (cartesian JOIN) use OUTER JOIN



Again, MySQL does not have a plain OUTER JOIN. If you want a full 
Cartesian product of two tables, use the 'comma join' syntax with no 
criteria for matching the tables in the WHERE clause.


SELECT ... FROM tableA, tableB WHERE 

Syntax details are located here:
http://dev.mysql.com/doc/refman/5.5/en/join.html



Finally: Be aware FUNCTIONS such as AVG cast off indexing and should be avoided 
unless the FUNCTION(columnName) itself is indexed
GROUP BY re-arranges your query so its best to introduce GROUP BY in stages



Not exactly. If you wrap a column in a function and attempt to use the 
results of that function in the WHERE clause, then you are correct. 
However based on the way your define your indexes, the data you process 
in a function may actually come from the index and save you a trip to 
the underlying table. In this case, the index could make your function 
faster by skipping an additional retrieval step.




use realistic alias names like Dept and EmployeeNumber and avoid aliases that 
cause confusion like 'a' or 'foo'


Excellent advice.


Develop in stages and write down what YOU EXPECT vs WHAT each query produces ..
If the Individual Select doesnt produce expected results..STOP and correct the 
SELECT Statement


Also excellent advice.


Diagram out what you expect results from any of the INNER JOIN, OUTER JOIN, 
LEFT JOIN or RIGHT JOIN operations will produce
If the executed JOIN Statement does not produce expected results STOP and 
correct the JOIN clause BEFORE incorporating more functionality

Obfuscation and confusion can hopelessly sidetrack any intelligent analysis


Well put!

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: help with correlated subquery

2012-08-21 Thread Rick James 
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.

 -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.8545833 |
 |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
 |   18 | 44 | 1,3  | 349.763 | 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

Re: help with correlated subquery

2012-08-21 Thread Larry Martell 
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.8545833 |
 |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
 |   18 | 44 | 1,3  | 349.763 | 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

Re: help with correlated subquery

2012-08-21 Thread Larry Martell 
On Tue, Aug 21, 2012 at 5:39 PM, Martin Gainty mgai...@hotmail.com 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.8545833 |
  | 18 | 44 | 1,2 | 121.798 | 121.83983335000 |
  | 18 | 44 | 1,3 | 349.763 | 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.001 |
| 44 | 1,2  | 121.7156667 |
| 44 | 1,2  | 121.964 |
| 44 | 1,3  | 349.790 |
| 44 | 1,3  | 349.710 |
++--+-+
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.798 |
|9 | 44 | 1,3  | 349.763 |
+--++--+-+
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.mart...@gmail.com
 To: mysql@lists.mysql.com


 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

Re: help with correlated subquery

2012-08-21 Thread hsv 
 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.8545833 |
|   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
|   18 | 44 | 1,3  | 349.763 | 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.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: help with correlated subquery

2012-08-21 Thread Larry Martell 
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.8545833 |
 |   18 | 44 | 1,2  | 121.798 | 121.83983335000 |
 |   18 | 44 | 1,3  | 349.763 | 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!!

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql