How to add column values in mysql

Question

This is my table data Student

enter image description here

And this is my query --

SELECT id, SUM( maths + chemistry + physics ) AS total, maths, chemistry, physics
FROM `student`

but it is throwing a single row --

id  total   maths   chemistry   physics
118     760     55  67  55

although i want to apply sum for all ids ....let me know how can i achieve this?

score 72 · Accepted Answer · answered Sep 12 '12 at 11:34

72

Sum is a aggregate function. You dont need to use it. This is the simple query -

select *,(maths + chemistry + physics ) AS total FROM `student`

answered Sep 12 '12 at 11:34

Piyu

736
5
4

digout · Answer 2 · 2018-10-23T18:59:16.103

19

Tip: If one of the fields has the possibility to be NULL, then use COALESCE to default these to 0, otherwise total will result in NULL.

SELECT *, (
    COALESCE(maths, 0) +
    COALESCE(chemistry, 0) +
    COALESCE(physics, 0)
) AS total 
FROM `student`

edited Oct 23 '18 at 18:59

answered Apr 25 '18 at 12:47

digout

4,041
1
31
38

is there an easier way if you have lots of columns you want to add than this? Maybe a database setting? – dasLort Jul 28 '21 at 12:58
@dasLort consider changing your table schema to be INT NOT NULL for example and a default of 0? – digout Mar 17 '22 at 14:25

score 11 · Answer 3 · answered Sep 12 '12 at 11:31

11

If you're requiring to get total marks of each student, then SUM is not what you'd be needing.

SELECT id,
    (maths+chemistry+physics) AS total,
    maths,
    chemistry,
    physics
FROM `student`

Will do the job just fine.

answered Sep 12 '12 at 11:31

hjpotter92

78,589
36
144
183

another fascinating answer..you guys are awesome....very helpful platform for a learner :) – Trialcoder Sep 12 '12 at 11:33

score 9 · Answer 4 · answered Sep 12 '12 at 11:32

9

You don't need use SUM for this operation. Try this query:

SELECT id, ( maths + chemistry + physics ) AS total, maths, chemistry, physics
FROM `student`

answered Sep 12 '12 at 11:32

khomyakoshka

1,259
8
18

Benson Kiprono · Answer 5 · 2019-05-25T15:50:05.053

1

The sum function in MySQL works in the sense that it gives you the sum of values in a column from your select statement. If you need to sum values from a row in your query, then just use plus (+) What you need is this query :

SELECT id, (`maths` +`chemistry`+`physics`) AS `total`, `maths`, `chemistry`, `physics`
FROM `student`;

This will give you the results you need.

edited May 25 '19 at 15:50

answered May 16 '19 at 11:16

Benson Kiprono

129
1
1
12

backtick before FROM is typo, is it not? – Jimmmy May 16 '19 at 11:46

score 0 · Answer 6 · answered Jul 23 '14 at 10:50

0

All aggregate function works on rows specified by rowname and group by operation. You need operation on individual rows which is not an option for any aggregate function.

answered Jul 23 '14 at 10:50

Saikat Biswas

114
1
9

score 0 · Answer 7 · answered Sep 21 '18 at 08:19

0

Try this

SELECT id, ( maths + chemistry + physics ) AS total, maths, chemistry, physics
FROM `student`

You are done. Thanks

answered Sep 21 '18 at 08:19

Y. Joy Ch. Singha

3,056
24
26

How to add column values in mysql

7 Answers7

Linked