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.
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.
Sample result:
SELECT mark1+mark2+mark3 FROM scorecard
You won't have to manually add all the columns anymore.
You could change the database structure such that all subject rows become a column variable (like spreadsheet). This makes such analysis much easier
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
SELECT student, SUM(mark1+mark2+mark3+....+markn) AS Total FROM your_table
SELECT student, (SUM(mark1)+SUM(mark2)+SUM(mark3)....+SUM(markn)) AS Total
FROM your_table
GROUP BY student
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`
//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'
Source: Stackoverflow.com