CODEWITHSUNDEEP
mysqlsql
rk3265423
rk3265423

Reputation: 1007

MySQL Sum() multiple columns

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.

Upvotes: 97

Views: 360789

Answers (8)

Harat
Harat

Reputation: 1366

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

Upvotes: 9

Ambarish
Ambarish

Reputation: 137

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'

Upvotes: 4

priya
priya

Reputation: 1

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

Upvotes: 0

TarranJones
TarranJones

Reputation: 4232

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;

Upvotes: 24

corwa
corwa

Reputation: 21

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

Upvotes: 1

majidarif
majidarif

Reputation: 19975

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.

Upvotes: 36

Jon Todd
Jon Todd

Reputation: 121

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

Upvotes: 7

Maulik patel
Maulik patel

Reputation: 2432

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

Upvotes: 131

Related Questions