我正在尝试从一个表中计算平均值,并将其放入一个新的表中,该表按
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);
?>