CODEWITHSUNDEEP
javascriptphpjquery
eloginko
eloginko

Reputation: 97

How to total average in data in table field

I have a table called

tb_applicants with fields 'id','aic','name','total'

My problem is how to total all the total of applicant with same aic field value and divide it with 7 and save the average score to a new table called: fapptotal with field 'id','aic','name','ftotal'?

can anyone help me with this im working with a scoreboard system in php...and im still learing..please

Upvotes: 2

Views: 81

Answers (3)

Control Freak
Control Freak

Reputation: 13243

Even though this question has entirely nothing to do with php, javascript or jquery, This should do it:

INSERT INTO fapptotal (id,aic,name,ftotal)
SELECT DISTINCT 
  t.id
 ,t.aic
 ,t.name
 ,(SELECT SUM(t2.total) FROM tb_applicants t2 WHERE t2.aic = t.aic)/7 thissum
FROM tb_applicants t
GROUP BY t.id,t.aic,t.name

Upvotes: 2

Phoenixcoded
Phoenixcoded

Reputation: 38

$SQL="SELECT id,aic,name,SUM(total/7) as Total FROM tb_applicants GROUP BY aic";
$res=mysql_query($SQL) or die('something wrong with query');


while($row=mysql_fetch_array($res))
{
    $id=$row['id'];
    $aic=$row['aic'];        
    $name=$row['name'];
    $ftotal=$row['Total'];
    $SQL="INSERT INTO fapptotal VALUES('$id','$aic','$name','$ftotal')";
    mysql_query($SQL) or die('wrong with query 2');

}

Your question is now totally solved

Upvotes: 0

voodoo417
voodoo417

Reputation: 12111

If i understood. Try it (cann`t check):

INSERT INTO `ftotal` (`id`,`aic`,`name`,`ftotal`)
SELECT id,aic,name, SUM(total)/7 AS ftotal
FROM `tb_applicants`
GROUP BY aic

Upvotes: 0

Related Questions