MySQL Sum() multiple columns

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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;

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

select ((max(assignment1))+(max(assignment2))+(max(assignment3))-(min(assignment1))+(min(assignment2))+(min(assignment3)))from box_scores;