mysql 根据组的个数计算_mysql – 计算多个列的平均值,并按group_id分组

我正在尝试从一个表中计算平均值,并将其放入一个新的表中,该表按

MySQL中的组名分组.

在第一个表格(称为答案)中,我有许多小组的调查答案.答案是数值(INT,1-7),问题分为问题组(文化,表现等).所有答案都有一个响应者姓名,他们都属于一个具有* group_id *的组.我想汇总答案并计算所有问题组和group_id分组的平均值.

例如.我们可以假设答案中的数据如下所示：

||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 ||

我现在想将问题1-3的平均值变为average_a,将问题4-6的平均值变为average_b,将所有组的不同表格(结果)与group_id分组

现在表格答案如下所示：

||group_id|average_a|average_b||

|| 1 | null | null ||

|| 2 | null | null ||

|| 3 | null | null ||

我想将结果表更新为如下所示：

||group_id|average_a|average_b||

|| 1 | 4,5556 | 3,1111 ||

|| 2 | 2,2222 | 4,7778 ||

|| 3 | 4,5556 | 3,6667 ||

编辑：

在原始答案表上添加一些细节：

+------------------------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------------------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| Respondent | varchar(20) | YES | | NULL | |

| Email | varchar(39) | YES | | NULL | |

| Website | varchar(60) | YES | | NULL | |

| Bransch | varchar(60) | YES | | NULL | |

| Koncern | varchar(60) | YES | | NULL | |

| Company | varchar(60) | YES | | NULL | |

| typ_av_enhet | varchar(60) | YES | | NULL | |

| avdelning | varchar(60) | YES | | NULL | |

| typ_av_avdelning | varchar(30) | YES | | NULL | |

| gruppid | varchar(5) | YES | | NULL | |

| survey_no | varchar(5) | YES | | NULL | |

| Grupp | varchar(28) | YES | | NULL | |

| group_type | varchar(60) | YES | | NULL | |

| Kön | varchar(1) | YES | | NULL | |

| Ålder | varchar(5) | YES | | NULL | |

| Samarbetsträning | varchar(1) | YES | | NULL | |

| Samarbetserfarenhet | varchar(1) | YES | | NULL | |

| TaskClarity1 | varchar(1) | YES | | NULL | |

| TaskClarity2 | varchar(1) | YES | | NULL | |

| TaskClarity3 | varchar(1) | YES | | NULL | |

这里也是我尝试使用@ stefan的说明的脚本：

$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
";

//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);

?>