SQL ARITHMETIC OPERATION

I am trying to get the sum of two rows using (+) operator in select. But when one column is null, the column for the SUM will also be null.

Select SALES_ID, FY19.SALES_2019, FY20.SALES_2020, (SALES_2019 + SALES_2020) as TOTAL_SALES
FROM
(Select Sales_id, sum(sales_amount) from sales_table where sales_year = '2019')FY19
LEFT JOIN
(Select Sales_id, sum(sales_amount) from sales_table where sales_year = '2020')FY20
ON FY19.sales_id = FY20.sales_id

If 2019 have sales amount of 20,000 and 2020 with 0 or null, the TOTAL_SALES will also be NULL I am wondering if there is a way to make the TOTAL_SALES be 20,000 too.

Answer

Why don’t you use the conditional aggregation as follows:

Select SALES_ID, 
       sum(case when sales_year = '2019' then sales_amount end) as SALES_2019, 
       sum(case when sales_year = '2020' then sales_amount end) as SALES_2020, 
       sum(sales_amount) as TOTAL_SALES
 FROM sales_table where sales_year in ('2019','2020')