CODEWITHSUNDEEP
sqlamazon-web-servicesprestoamazon-athena
Evgenia
Evgenia

Reputation: 13

Gracefully sum multiple columns in AWS Athena excluding nulls

I have to create total_costs, total_proceeds, and total_net_loss columns by adding few other columns in AWS Athena. I also have to replace nulls with zeros, otherwise the result will be null. I tried to use sum() function but it works only on the single column. The following query accomplishes what I want to do but looks very ungracefully. Is there any other, more elegant way to sum columns in Athena?

SELECT loan_identifier,
       monthly_reporting_period,
       current_actual_upb, 
       current_loan_delinquency_status, 
       (COALESCE(foreclosure_costs,0) + COALESCE(property_preservation_and_repair_costs,0) + COALESCE(asset_recovery_costs,0) + COALESCE(miscellaneous_holding_expenses_and_credits,0) + COALESCE(associated_taxes_for_holding_property,0)) AS total_costs,
       (COALESCE(net_sale_proceeds,0) + COALESCE(credit_enhancement_proceeds,0) + COALESCE(repurchase_make_whole_proceeds,0) + COALESCE(other_foreclosure_proceeds,0)) AS total_proceeds,
       current_actual_upb + (current_actual_upb * ((current_interest_rate/100 - 0.0035)/12)*DATE_DIFF('day', last_paid_installement_date, disposition_date)/30) + (COALESCE(foreclosure_costs,0) + COALESCE(property_preservation_and_repair_costs,0) + COALESCE(asset_recovery_costs,0) + COALESCE(miscellaneous_holding_expenses_and_credits,0) + COALESCE(associated_taxes_for_holding_property,0)) - (COALESCE(net_sale_proceeds,0) + COALESCE(credit_enhancement_proceeds,0) + COALESCE(repurchase_make_whole_proceeds,0) + COALESCE(other_foreclosure_proceeds,0)) AS total_net_loss
FROM performance_data;

Upvotes: 1

Views: 6756

Answers (1)

Theo
Theo

Reputation: 132922

Use SUM and COALESCE in combination, like this:

SELECT SUM(COALESCE(y, 0) + COALESCE(z, 0)) AS aggregate_sum_of_y_and_z
FROM (
  VALUES (1, 2),
         (NULL, 3),
         (2, 1)) AS x (y, z)

Upvotes: 2

Related Questions