Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In Below table.....I want to perform addition operation in M1 and M2 columns through query.


EMP_ID M1 M2
1 10 20
2 20 NULL
3 NULL 50
4 40 70
5 50 NULL

Result table be like below.

EMP_ID TOTAL
1 30
2 20
3 50
4 110
5 50

Can anyone help with this.

Thanks!

What I have tried:

select EMP_ID, (M1+M2) AS TOTAL from table1;
Posted
Updated 22-Jan-19 1:41am
Comments
Santosh kumar Pithani 22-Jan-19 4:45am    
your query looks good;getting any error?If you found null type exception use like this "NVL(M1,0)+NVL(M2,0)"
Richard Deeming 22-Jan-19 7:19am    
That sounds like the answer to me. You should post it as a solution. :)
Member 14124697 22-Jan-19 7:19am    
Thanks.

Results looks good now with NVL command.

Can you describe me in below query why we used (Column_Name,0) for better understanding.

NVL(M1,0)+NVL(M2,0)
CHill60 22-Jan-19 7:32am    
The function NVL is saying if there is a null value in the column M1 then use 0 otherwise use the value found in the column. If we had used
NVL(M1,100)
instead then it would use 100 wherever there was a NULL otherwise it would use the value in column M1.
Does that help?
Member 14124697 22-Jan-19 7:36am    
Great.....understood clearly...

Thanks ur help every time

1 solution

SQL
--NVL(p1,p2) function has two parameters.If p1 is null then its take p2 AS values

--EX:-NVL(M1,0) means "M1" columns as null value then its take "0" instead of null values.

--In your query M1,M2 columns has null values so arithmetic operation is not possible so NVL function is used.

SELECT EMP_ID
      ,(NVL(M1,0)+NVL(M2,0)) AS TOTAL
  FROM table1
 
Share this answer
 
Comments
Member 14124697 22-Jan-19 7:43am    
Thanks santosh...

I got the answer
Santosh kumar Pithani 22-Jan-19 7:47am    
NVL(m1,0) takes only two parameters but coalesce(m1,m2,0) function work for more than two parameters.Give me stars

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900