How to add column values in mysql

问题:

This is my table data Student

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?

回答1:

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

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

回答2:

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.

回答3:

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

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

回答4:

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`

回答5:

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.

回答6:

Try this

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

You are done. Thanks