Calculate the average from several columns and group by group_id

Question

I'm trying to calculate the averages from one table and put it in a new table that is grouped by group name in MySQL.

In the first table (called answers) I have survey answers from many groups. The answers are numerical values (INT, 1-7) and questions are grouped into question groups (culture, performance, etc.). All answers have a respondent name and they all belong to a group with a *group_id*. I'd like to aggregate the answers and calculate averages for all question groups and group by group_id.

For example. We can assume the data in answers looks like this:

||respondent | group_id | question_1 | question_2 | question_3| question_4 | question_5 | question_6||
||Joe        |1         |4           |3           |5          |4           |2           |2          ||
||Jane       |1         |3           |6           |6          |2           |1           |6          ||
||Jones      |1         |7           |3           |4          |1           |6           |4          ||
||Harry      |2         |2           |2           |3          |7           |5           |3          ||
||Pete       |2         |3           |5           |1          |4           |4           |5          ||
||Frank      |2         |1           |1           |2          |2           |7           |6          ||
||Sam        |3         |6           |7           |4          |6           |2           |2          ||
||Kim        |3         |3           |3           |6          |5           |1           |1          ||
||Todd       |3         |1           |4           |7          |4           |5           |7          ||

I'd now like to get the average for questions 1-3 into average_a and the averages from questions 4-6 into average_b into a different table (results) with all groups and grouped by group_id

Right now table answers looks like this:

 ||group_id|average_a|average_b||<br>
    ||   1    | null  | null   ||<br>
    ||   2    |  null | null  ||<br>
    ||   3    |  null | null  ||<br>

I'd like to UPDATE the results table to look like this:

||group_id|average_a|average_b||
||   1    |  4,5556 |  3,1111 ||
||   2    |  2,2222 |  4,7778 ||
||   3    |  4,5556 |  3,6667 ||

EDIT:

Adding some details on the original answers table:

+------------------------+------------------+------+-----+---------+----------------+<br>
| Field                  | Type             | Null | Key | Default | Extra          |<br>
+------------------------+------------------+------+-----+---------+----------------+<br>
| id                     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |<br>
| Respondent             | varchar(20)      | YES  |     | NULL    |                |<br>
| Email                  | varchar(39)      | YES  |     | NULL    |                |<br>
| Website                | varchar(60)      | YES  |     | NULL    |                |<br>
| Bransch                | varchar(60)      | YES  |     | NULL    |                |<br>
| Koncern                | varchar(60)      | YES  |     | NULL    |                |<br>
| Company                | varchar(60)      | YES  |     | NULL    |                |<br>
| typ_av_enhet           | varchar(60)      | YES  |     | NULL    |                |<br>
| avdelning              | varchar(60)      | YES  |     | NULL    |                |<br>
| typ_av_avdelning       | varchar(30)      | YES  |     | NULL    |                |<br>
| gruppid                | varchar(5)       | YES  |     | NULL    |                |<br>
| survey_no              | varchar(5)       | YES  |     | NULL    |                |<br>
| Grupp                  | varchar(28)      | YES  |     | NULL    |                |<br>
| group_type             | varchar(60)      | YES  |     | NULL    |                |<br>
| Kön                    | varchar(1)       | YES  |     | NULL    |                |<br>
| Ålder                  | varchar(5)       | YES  |     | NULL    |                |<br>
| Samarbetsträning       | varchar(1)       | YES  |     | NULL    |                |<br>
| Samarbetserfarenhet    | varchar(1)       | YES  |     | NULL    |                |<br>
| TaskClarity1           | varchar(1)       | YES  |     | NULL    |                |<br>
| TaskClarity2           | varchar(1)       | YES  |     | NULL    |                |<br>
| TaskClarity3           | varchar(1)       | YES  |     | NULL    |                |<br>

Also here is the script that I tried using @stefan's instructions:

    <?php
$username = "root";
$password = "root";
$hostname = ":/Applications/MAMP/tmp/mysql/mysql.sock";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
 or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a database to work with
$selected = mysql_select_db("ca",$dbhandle) 
  or die("Could not select ca");

//delete old table
$delete_sql="Drop Table results";
// Execute query
if (mysql_query($delete_sql))
  {
  echo "Table deleted successfully";
  }
else
  {
  echo "Error deleting table: " . mysql_error();
  }

//create table
$sql="CREATE TABLE results(Group_id INT PRIMARY KEY, TaskClarity FLOAT)";

// Execute query
if (mysql_query($sql))
  {
  echo "Table results created successfully";
  }
else
  {
  echo "Error creating table: " . mysql_error();
  }



//Calculate task clarity
$task_clarity = "
INSERT INTO results (
    Group_id,
    TaskClarity
)
(
   SELECT
     gruppid ,
     AVG((TaskClarity1 + TaskClarity2 + TaskClarity3)/3) as average_task_clarity
   FROM
     answers
   GROUP BY
     gruppid
) ON DUPLICATE KEY UPDATE TaskClarity = VALUES(TaskClarity)"
;
$resource_retrive_task_clarity = mysql_query($task_clarity);    //execute the query
if (! $resource_retrive_task_clarity = mysql_query($task_clarity) ){
    echo "Error reading from table";
    die;
}

if (! mysql_num_rows($resource_retrive_task_clarity ) ){
    echo "No records found in table";
}
else {
echo "funkar";

}

//close the connection
mysql_close($dbhandle);
?>

Answer 1

Try this:

EDIT to update the results table (assuming that group_id column has UQ or PK index)

INSERT INTO results (
    group_id,
    average_a,
    average_b
)
(
   SELECT
     group_id ,
     AVG((question_1 + question_2 + question_3)/3) as za_average_a,
     AVG((question_4 + question_5 + question_6)/3) as za_average_b
   FROM
     answers
   GROUP BY
     group_id
) ON DUPLICATE KEY UPDATE 
     average_a = VALUES(average_a), 
     average_b = VALUES(average_b)

UPDATE The example is made here and it works