MySQL Sum() 多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22369336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Sum() multiple columns
提问by rk3265423
I have a table of student scorecard. here is the table,
我有一张学生记分卡表。这是桌子，
subject | mark1 | mark2 | mark3 |......|markn
stud1 | 99 | 87 | 92 | | 46
stud2 |....................................
.
.
studn |....................................|
Now, i need to sum it for each student of total marks. I got it by using sum(mark1+mark2+...+markn) group by stud. I want to know how to sum it without adding each column name,it will be huge when in case up to marks26. so could anyone know how to fix it. Thanks in advance.
现在，我需要为每个学生的总分求和。我通过使用得到它sum(mark1+mark2+...+markn) group by stud。我想知道如何在不添加每个列名的情况下对其进行求和，以防万一marks26。任何人都可以知道如何解决它。提前致谢。
回答by Maulik patel
SELECT student, (SUM(mark1)+SUM(mark2)+SUM(mark3)....+SUM(markn)) AS Total
FROM your_table
GROUP BY student
回答by majidarif
Another way of doing this is by generating the select query. Play with this fiddle.
另一种方法是生成选择查询。玩这个小提琴。
SELECT CONCAT('SELECT ', group_concat(`COLUMN_NAME` SEPARATOR '+'), ' FROM scorecard')
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (select database())
AND `TABLE_NAME` = 'scorecard'
AND `COLUMN_NAME` LIKE 'mark%';
The query above will generate another query that will do the selecting for you.
上面的查询将生成另一个查询，它将为您进行选择。
- Run the above query.
- Get the result and run that resulting query.
- 运行上面的查询。
- 获取结果并运行该结果查询。
Sample result:
示例结果：
SELECT mark1+mark2+mark3 FROM scorecard
You won't have to manually add all the columns anymore.
您不必再手动添加所有列。
回答by Harat
SELECT student, SUM(mark1+mark2+mark3+....+markn) AS Total FROM your_table
SELECT student, SUM(mark1+mark2+mark3+....+markn) AS Total from your_table
回答by TarranJones
If any of your markncolumns are "AllowNull" then you will need to do a little extra to insure the correct result is returned, this is because 1 NULL value will result in a NULL total.
如果您的任何markn列是“AllowNull”，那么您需要做一些额外的工作以确保返回正确的结果，这是因为 1 个 NULL 值将导致 NULL 总数。
This is what i would consider to be the correct answer.
这就是我认为的正确答案。
SUM(IFNULL(`mark1`, 0) + IFNULL(`mark2`, 0) + IFNULL(`mark3`, 0)) AS `total_marks`
IFNULLwill return the 2nd parameter if the 1st is NULL. COALESCEcould be used but i prefer to only use it if it is required. See What is the difference bewteen ifnull and coalesce in mysql?
如果第一个参数为 NULL，则IFNULL将返回第二个参数。 可以使用COALESCE，但我更喜欢仅在需要时才使用它。请参阅mysql中的ifnull和coalesce有什么区别？
SUM-ing the entire calculation is tidier than SUM-ing each column individually.
SUM -整个计算比 SUM 单独每个列更整洁。
SELECT `student`, SUM(IFNULL(`mark1`, 0) + IFNULL(`mark2`, 0) + IFNULL(`mark3`, 0)) AS `total_marks`
FROM student_scorecard
GROUP BY `student`
回答by Ambarish
//Mysql sum of multiple rows Hi Here is the simple way to do sum of columns
//Mysql 多行求和嗨这里是做列求和的简单方法
SELECT sum(IF(day_1 = 1,1,0)+IF(day_3 = 1,1,0)++IF(day_4 = 1,1,0)) from attendence WHERE class_period_id='1' and student_id='1'
回答by Jon Todd
The short answer is there's no great way to do this given the design you have. Here's a related question on the topic: Sum values of a single row?
简短的回答是，鉴于您拥有的设计，没有什么好方法可以做到这一点。这是关于该主题的相关问题：Sum values of a single row？
If you normalized your schema and created a separate table called "Marks" which had a subject_id and a mark column this would allow you to take advantage of the SUM function as intended by a relational model.
如果您规范了您的架构并创建了一个名为“Marks”的单独表，该表具有一个 subject_id 和一个标记列，这将允许您按照关系模型的预期利用 SUM 函数。
Then your query would be
那么你的查询将是
SELECT subject, SUM(mark) total
FROM Subjects s
INNER JOIN Marks m ON m.subject_id = s.id
GROUP BY s.id
回答by corwa
You could change the database structure such that all subject rows become a column variable (like spreadsheet). This makes such analysis much easier
您可以更改数据库结构，使所有主题行都成为列变量（如电子表格）。这使得这样的分析更容易
