CODEWITHSUNDEEP
phpmysqlunion
Debasish Choudhury
Debasish Choudhury

Reputation: 83

simple way to calculate average by union of three tables using MySQL and php

I have three database tables

        Graduate_survey
      g_id(pk)AI  PO  PO1  PO2 Program_name year1 year2

        Alumni_survey
      a_id(pk)AI  PO  PO1  PO2 Program_name year1 year2

        faculty_survey
      f_id(pk)AI  PO  PO1  PO2 Program_name year1 year2

after inserting the values into these tables i need to calculate the average and store the average value in another table total_survey

   total_survey
   t_id(pk)AI  PO  PO1  PO2 Program_name year1 year2

The Program_name, year1, year2 are inserted using $_session. How to calculate the average using MySQL query and store the new value in another table using php? My codes are given below but it is not working.

             <?php
             @include("connection.php");
             error_reporting(0);
             $program=$_POST['Program_name'];
             $year1=$_POST["year1"];
             $year2=$_POST["year2"];
             $po=$_POST["total_PO"];
             $po1=$_POST["total_PO1"];
             $po2=$_POST["total_PO2"];
             $_SESSION['SProgram_name']=$program;
             $_SESSION['Syear1']=$year1;
             $_SESSION['Syear2']=$year2;
             if($_POST['submit'])
            {
              $sql= "SELECT AVG(t.PO) AS total_PO, AVG(t.PO1) AS 
              total_PO1, AVG(t.PO2) AS total_PO2
              FROM ( SELECT PO,PO1,PO2
              FROM Graduate_survey
              UNION ALL
              SELECT PO,PO1,PO2
              FROM alumni_survey
              UNION ALL
              SELECT PO,PO1,PO2
              FROM faculty_survey
              )*t";
             $rData=mysql_query($sql);
             $res=mysql_fetch_array($rData);

              $sql="select * from total_survey";
             $rData=mysql_query($sql);
             $res=mysql_fetch_array($rData);
             $sql="insert into total_survey 
            values('','$po','$po1','$po2','$program','$year1','$year2')";
             mysql_query($sql);

             }
            ?>
           <form method="post">
           <p align="center"><input type="submit" name="submit" value="Click 
           here to calculate the final indirect assesment">
           </form>

suppose in the graduate_survey table the PO column contain 70, PO1 contain 60 and PO2 contain 80. In the alumni_survey the PO column contain 60, PO1 contain 70 and PO2 contain 90.In the faculty_survey the PO column contain 90, PO1 contain 80 and PO2 contain 60. All i need is Average(PO+PO+PO), Average(PO1+PO1+PO1), Average(PO2+PO2+PO2) by union of all the three table group by ID

Upvotes: 0

Views: 341

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You need to do average on UNIONs and wrap it into INSERT query, e.g.:

INSERT INTO total_survey (PO, PO1, PO2)
SELECT AVG(PO), AVG(PO1), AVG(PO2) 
FROM (
 SELECT PO, PO1, PO2 FROM graduate_survey

 UNION

 SELECT PO, PO1, PO2 FROM Alumni_survey

 UNION

 SELECT PO, PO1, PO2 FROM faculty_survey
) a;

Upvotes: 1

Related Questions