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.
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
I was just going through your php code ...
I have done some changes in your code ..please have a look
<?php
mysql_connect('localhost','root','');
mysql_select_db('test');
$qry="SELECT distinct 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 yable_B WHERE b_ID = '$value'";
$result= mysql_query($qry);
while($row = mysql_fetch_assoc($result)) {
$a_ID[$value][] =$row['a_ID'];
}
}
foreach( $a_ID as $value)
{
$search = implode(',',$value);
echo $search ."<br>";
}
?>
select distinct b_ID, (select avg(avg_data) from table_A where a_ID in (select a_ID from table_B y where y.b_ID=x.b_ID) ) from table_B x