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

4

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

edited Oct 2, 2018 at 13:19

answered Oct 2, 2018 at 13:06

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

2

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.

answered Oct 2, 2018 at 13:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

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

0

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

edited Oct 2, 2018 at 13:32

answered Oct 2, 2018 at 13:22

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

Collectives™ on Stack Overflow

Mysql : Add values of columns in table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related