Mysql : Add values of columns in table

Question

I have this structure in my table

id|source_id|val1|val2 1 |1 |10 |NULL 2 |1 |NULL|3 3 |2 |NULL|5 4 |2 |4 |NULL 5 |3 |7 |NULL

ANd i want to have this in mysql

source_id|total 1 |13 2 |9 3 |7

Can you help me please

Madhur Bhaiya · Accepted Answer · 2018-10-02 13:19:49Z

Use Ifnull() function to replace null values with 0.
Use Sum() function to sum both val1 and val2 in a single expression, using Group By on source_id.

Do the following:

SELECT ressource_id, SUM(IFNULL(val1, 0)) + SUM(IFNULL(val2, 0)) AS total FROM your_table GROUP BY ressource_id

Gordon Linoff · Accepted Answer · 2018-10-02 13:07:44Z

I think you want:

select source_id, coalesce(sum(val1), 0) + coalesce(sum(val2), 0) from t group by source_id;

I would do the NULL conversion after the SUM(), because some source_ids only have NULLs in one of the columns.

Rubin Porwal · Accepted Answer · 2018-10-02 13:32:17Z

SELECT q.resource_id, ( q.val1 + q.val2 ) AS total FROM (SELECT resource_id, Ifnull(Sum(val1), 0) AS val1, Ifnull(Sum(val2), 0) AS val2 FROM `table` GROUP BY resource_id) AS q

Collectives™ on Stack Overflow

Mysql : Add values of columns in table

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related