sql - Average Multiple Columns in Postgres

Question

I have a table with multiple rows, each of which contains these four columns:

product	price_instore	price_pickup	price_delivery	price_ship
1	$13.00	$13.50	$14.50	$18.00.
2	$4.00	$4.00	NULL	NULL
3	$10.00	$10.00	$12.00	NULL

question from:https://stackoverflow.com/questions/65854097/average-multiple-columns-in-postgres

Answer 1

Try this

  select coalesce (PRICE_INSTORE, 0 ) +  coalesce (PRICE_PICKUP, 0) + coalesce (PRICE_DELIVERY , 0) + coalesce (PRICE_SHIP , 0)  / 
 ((case when PRICE_INSTORE = null then 0 else 1 end) + (case when PRICE_PICKUP = null then 0 else 1 end) +
 (case when PRICE_DELIVERY = null then 0 else 1 end) + (case when PRICE_SHIP = null then 0 else 1 end) )
 from product