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  Thanks in advance

User · Answer

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

User · Answer

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

User · Answer

SELECT student   SUM mark1  SUM mark2  SUM mark3      SUM markn   AS Total  FROM your table  GROUP BY student

User · Answer

SELECT student  SUM mark1 mark2 mark3      markn  AS Total FROM your table

User · Answer

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

User · Answer

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

User · Answer

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    Run the above query  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] MySQL Sum() multiple columns

Examples related to mysql

Examples related to sql