我正在尝试计算一张表的平均值，并将其放入按 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||<br>
|| 1 | null | null ||<br>
|| 2 | null | null ||<br>
|| 3 | null | null ||<br>
我想将结果表更新为如下所示:
||group_id|average_a|average_b||
|| 1 | 4,5556 | 3,1111 ||
|| 2 | 2,2222 | 4,7778 ||
|| 3 | 4,5556 | 3,6667 ||
编辑:
在原始答案表上添加一些详细信息:
+------------------------+------------------+------+-----+---------+----------------+<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>
这里也是我使用@stefan 的说明尝试的脚本:
<?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);
?>
最佳答案
试试这个:
编辑 更新结果表(假设 group_id 列有 UQ 或 PK 索引)
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 该示例制作为 here 并且它有效
关于mysql - 计算几列的平均值并按 group_id 分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16142137/