Is there any simple way in MySQL to insert values in a table that are fetched from another table

Question

I have three database tables

    Graduate_survey
    id(pk)AI  PO1  PO2  PO3 

    Alumni_survey
    id(pk)AI  PO1  PO2  PO3 

    faculty_survey
    id(pk)AI  PO1  PO2  PO3

after inserting the values into these tables and calculating the average by union of three tables now i have to store the average value in another table total_survey

total_survey id(pk)AI PO PO1 PO2 My codes are

               <?php 
                @include("connection.php");
               if($_POST['submit'])
                 {
                 $sql= "SELECT t.id
               , AVG(t.PO1) AS total_PO1, AVG(t.PO2) AS total_PO2, 
                 AVG(t.PO3) AS total_PO3
                 FROM ( SELECT Graduate_survey.id
                , Graduate_survey.PO1,  Graduate_survey.PO2,  
                 Graduate_survey.PO3
                FROM Graduate_survey
                 UNION 
                 SELECT alumni_survey.id
                 , alumni_survey.PO1, alumni_survey.PO2, alumni_survey.PO3
                FROM alumni_survey
                 UNION
                 SELECT faculty_survey.id
                , faculty_survey.PO1, faculty_survey.PO2, faculty_survey.PO3
                 FROM faculty_survey
                 )t
                GROUP BY t.id 
                ORDER BY t.id";
               $rData=mysql_query($sql);
               $res=mysql_fetch_array($rData);
               $sql="insert into total_survey 
              values('','total_PO1','total_PO2','total_PO3')";
               mysql_query($sql);

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

select query is working properly but insert query is not working. The problem is i have to insert the datas that are shown by the select query in another table total_survey. How to store the fetched data in another table in MySQL?

Answer 1

You will need to replace some of my generic naming, but below is what you're after

INSERT INTO `SchemaName`.total_survey (`ColumnName1`, `ColumnName2`, `ColumnName3`, `ColumnName4`)

SELECT t.id, AVG(t.PO1) AS total_PO1, AVG(t.PO2) AS total_PO2, AVG(t.PO3) AS total_PO3
FROM ( SELECT Graduate_survey.id, Graduate_survey.PO1, Graduate_survey.PO2, Graduate_survey.PO3
        FROM Graduate_survey

        UNION 

        SELECT alumni_survey.id, alumni_survey.PO1, alumni_survey.PO2, alumni_survey.PO3
        FROM alumni_survey

        UNION

        SELECT faculty_survey.id, faculty_survey.PO1, faculty_survey.PO2, faculty_survey.PO3
        FROM faculty_survey
 )t
GROUP BY t.id 
ORDER BY t.id