How to SUM Columns value in MYSQL

We require the sum of column values to display or use in the programming skill. So today you see How to SUM Columns value in MYSQL. Learn More: How to Select Data Into MySQL Database Using PHP ? How to Create Event Calendar with jQuery, PHP, and MySQL

Problem Statement:
subject | mark1 | mark2 | mark3 |mark4
student1 | 99 | 87 | 92 | 46

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.

Solution:

Multiple solution propose for this: Here are the few:

Solution: 1

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

Another way of doing this is by generating the select query. 

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.


Leave a Reply