Select the column not like and combine column in one table [duplicate]

Question

My question is how can I select the currency not equal to USD and CNY and rename as others. Also, combining it into a new column AMT_Others.

ID     Name       Date          AMT_HKD    AMT_JPY   AMT_USD   AMT_TWD   AMT_CNY   
1      Amy     01/04/2022         0          5000       0        0          0
2      Bill    01/03/2022        200          0         0        0          0
3      Cathy   02/02/2022         0           0        80        2000      200

Result:

ID     Name       Date         AMT_Others    
1      Amy     01/04/2022        5000      
2      Bill    01/03/2022        200 
3      Cathy   02/02/2022        2000 

my code: (It cannot generate what I want, what should be added? Thanks)

select ID, Name, Date, (AMT_HKD, AMT_JPY and AMT_TWD) as AMT_Others

Answer 1

basically add them up as long as we have only one non zero value for all currencies except USD and CNY

Select ID, Name, Date,
AMT_HKD + AMT_JPY + AMT_TWD as AMT_Others
from yourtable

Answer 2

You can get your desired result by using + instead of comma or AND:

SELECT id, name, ydate, amt_hkd + amt_jpy + amt_twd AS amt_others FROM yourtable;

But this is quite unpleasant in case there are null values in some of your columns. In this case, the sum would be null, too, even if some of the columns are not null. You can use COALESCE to prevent this and to force building a sum. Null values will be replaced by 0 (or another value if you change it):

SELECT id, name, ydate, 
COALESCE(amt_hkd,0) + COALESCE(amt_jpy,0) + COALESCE(amt_twd,0) AS amt_others 
FROM yourtable;

You can check this and see the difference between the two query results here: db<fiddle

You should also prevent table names or column names that are SQL key words, like the "date" column in your example. That' s why I renamed it in my answer. Of course, if you can't change this, you have to live with it, but if you can, you should choose another name.

Answer 3

the other answers will work, unless there can be null values, if there is a row where one of these columns is ´null` then the result will also be null

select 1 + 2        -- output is 3
select 1 + 2 + null -- output is null

solution to this problem

isnull(AMT_HKD, 0)  -- easy but slow

COALESCE(amt_hkd, 0) -- easy but still not the fastest solution

case when amt_hkd is null then 0 else amt_hkd end -- fastest

Answer 4

SELECT ID, Name, Date,
AMT_HKD + AMT_JPY + AMT_TWD as AMT_Others
FROM tb_name;

This will work