获取表A中的几个SQL行（a_ID和avg_data）的平均值，其中表B（a_ID按b_ID分组）

Question

I have 2 tables. Table A, Table B.

Table A:

a_ID (313, 314, 315, 322) avg_data (50, 920, 12, 16)

Table B:

a_ID (313, 314, 315, 316, 317, 318, 319, 320, 321, 322, 323) 
b_ID (204, 204, 204, 205, 205, 206, 207, 208, 209, 210, 210)

So multiple a_ID belong to one b_ID.

I want to get the average of avg_data in table A for all of the a_ID that belong to b_ID in table B. I am trying to do this in php.

Something like:

 GET the list of a_ID in table B that have the same b_ID, then search table_A for all these ID's in the list and get the average of their respective avg_data column.

Is this possible to do just in one SQL statement?

So far I have tried

 $qry="SELECT b_ID
    FROM table_B";              

 $result= mysql_query($qry);

 while($row = mysql_fetch_assoc($result)) {

    $[b_ID] =$row['b_ID'];
  }

  foreach($b_ID as $value) {

  $qry="SELECT a_ID
    FROM table_B WHERE b_ID = '$value'";                

  $result= mysql_query($qry);


  while($row = mysql_fetch_assoc($result)) {

      $a_ID[] =$row['a_ID'];



   }

   $search = implode(', ', $a_ID); 

   echo $search; 

  }   

So this should output

 313, 314, 315 <br> 316, 317 <br> 318 <br> 319 etc

but it actually outputs:

 313, 314, 315, 322, 313, 314, 315

 313, 314, 315, 322, 313, 314, 315, 316, 317

 313, 314, 315, 322, 313, 314, 315, 316, 317, 318

This is meant to get a list of the a_ID's that belong to each b_ID so that I can then find the average, however, output is not correct and contains more a_ID's than it should.

I've tried to explain this as well as possible, but it doesnt seem to very good.

Answer 1

Ok - after comments I think I understand that your ultimate goal is the averages of avg_data. This can be done in a single rather simple query performing an INNER JOIN between the two tables and grouping by b_ID.

SELECT
  Tableb.b_ID,
  AVG(TableA.avg_data) AS average
FROM
  TableB
  INNER JOIN TableA ON TableA.a_ID = TableB.a_ID
GROUP BY TableB.b_ID

http://sqlfiddle.com/#!2/349ba/12

Now, if you also want the a_ID listed in the output, it gets a bit more complicated. You need to join the result of that as a subquery back against TableB to get the a_ID list, then they can be grouped via GROUP_CONCAT() into a comma-separated list of values which share the same average:

SELECT 
  GROUP_CONCAT(b.a_ID), 
  avgs.average
FROM 
  TableB b
  /* Makes sure only a_ID existing in TableA appear in the output */
  INNER JOIN TableA a ON b.a_ID = a.a_ID
  INNER JOIN (
    /* Retrieves average avg_data for like b_ID values */
    SELECT
      Tableb.b_ID,
      AVG(TableA.avg_data) AS average 
    FROM
      TableB
      INNER JOIN TableA ON TableA.a_ID = TableB.a_ID
    GROUP BY TableB.b_ID
  ) avgs ON b.b_ID = avgs.b_ID
GROUP BY avgs.b_ID

http://sqlfiddle.com/#!2/349ba/21