Geek Answers Handbook

Calculate average value from multiple columns and group by group_id

I am trying to calculate averages from a single table and put it in a new table, which is grouped by group name in MySQL.

In the first table (the so-called answers) I have answers from many groups. Answers are numerical values ​​(INT, 1-7), and questions are grouped into groups of questions (culture, productivity, etc.). All answers have the name of the respondent, and they all belong to the group with * group_id *. I would like to summarize the answers and calculate the average values ​​for all question groups and group_id.

For instance. We can assume that the data in the responses 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 ||

Now I would like to get the average value for questions 1-3 in average_a and the average of questions 4-6 in average_b in another table (results) with all groups and group by group_id

Right now, the table answers look like this:

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

I would 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 to the source response 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 I tried using @stefan 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); ?>
+4
source share
3 answers

Try the following:

EDIT to update the result table (assuming the group_id column has a 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 An example is made here and it works

+8
source

You can do the following:

  INSERT INTO new_table(`group_id`,`average_a`,`average_b`) SELECT group_id,(AVG(question_1 ) + AVG(question_2 ) + AVG(question_3 ))/3, (AVG(question_4 ) + AVG(question_5 ) + AVG(question_6 ))/3 FROM old_table GROUP BY group_id
+2
source

You can use this:

 SELECT GROUP_ID ,(SUM(question_1)+SUM(question_2)+SUM(question_3))/(3*COUNT(GROUP_ID)) AS Avg1 ,(SUM(question_4)+SUM(question_5)+SUM(question_6))/(3*COUNT(GROUP_ID)) AS Avg2 FROM answers GROUP BY Group_Id;

Here in /(3*COUNT(GROUP_ID) 3 is the number of columns (q1, q2, q3), and COUNT(GROUP_ID) is the number of rows for each GROUP_ID

See this SQLFiddle

+1
source

More articles:


All Articles