MySQLSum()多列

我有一张学生成绩单。 这是桌子,

subject  | mark1 | mark2 | mark3 |......|markn
stud1    | 99    | 87    | 92    |      | 46
stud2    |....................................
.
.
studn    |....................................|

现在，我需要对每个学生的总分进行求和。我是用 sum(mark1+mark2+...+markn) group by stud算出来的。我想知道如何在不添加每个列名的情况下对它求和，当最大值为 marks26时，它将是巨大的。有人知道怎么修吗。先谢谢你。

313879 次浏览
小开 
SELECT student, (SUM(mark1)+SUM(mark2)+SUM(mark3)....+SUM(markn)) AS Total
FROM your_table
GROUP BY student
小开

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?

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.

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

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.

  1. Run the above query.
  2. 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.

小开

//Mysql sum of multiple rows Hi Here is the simple way to do sum of columns

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'
小开

You could change the database structure such that all subject rows become a column variable (like spreadsheet). This makes such analysis much easier

小开

SELECT student, SUM(mark1+mark2+mark3+....+markn) AS Total FROM your_table

小开

If any of your markn columns 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.

This is what i would consider to be the correct answer.

SUM(IFNULL(`mark1`, 0) + IFNULL(`mark2`, 0) + IFNULL(`mark3`, 0)) AS `total_marks`

IFNULL will return the 2nd parameter if the 1st is NULL. COALESCE could be used but i prefer to only use it if it is required. See What is the difference bewteen ifnull and coalesce in mysql?

SUM-ing the entire calculation is tidier than SUM-ing each column individually.

SELECT `student`, SUM(IFNULL(`mark1`, 0) + IFNULL(`mark2`, 0) + IFNULL(`mark3`, 0)) AS `total_marks`
FROM student_scorecard
GROUP BY `student`

i want to know how to sum it without adding each column name,it will be huge when in case up to marks26

To generate and execute this statement dynamically in sql you would need to use the INFORMATION_SCHEMA.COLUMNS table to create a query string then execute it using a prepared statement saved in a variable.

SELECT CONCAT('SELECT `student`, SUM(IFNULL(`', group_concat(`COLUMN_NAME` SEPARATOR '`, 0) + IFNULL(`'), '`, 0) AS `total_marks` FROM `student_scorecard` GROUP BY `student`')
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA` = (select DATABASE())
AND `TABLE_NAME`   = 'student_scorecard'
AND `COLUMN_NAME` LIKE 'mark%'
# adapted from https://stackoverflow.com/a/22369767/2273611




# insert statement sql into a variable
INTO @statement_var;


#prepare the statement string
PREPARE stmt_name FROM @statement_var;


#execute the prepared statement/query
EXECUTE stmt_name;


# release statement
DEALLOCATE PREPARE stmt_name;

提交答案