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

I'd like to have a fifth column average_price that gives the average price for a product, but does not count NULLS towards the sum price, or towards the count used to divide the average.

So average price of product 1: ($13+$13.50+$14.50+$18)/4=$14.75
Average price of product 2: ($4+$4)/2 = $4.00
Average price of product 3: ($10+$10+$12)/3 = $10.67

Is there any way to do this is SQL? I've tried subqueries such as the one below without success:

(select coalesce((PRICE_INSTORE + PRICE_PICKUP + PRICE_DELIVERY + PRICE_SHIP) / 4, 
PRICE_INSTORE, PRICE_PICKUP, PRICE_DELIVERY, PRICE_SHIP)

but then I only get one price if any of them are null

Why not coalesce each of the four columns instead of their sum? — Johnny Fitz
– Johnny Fitz, Commented Jan 22, 2021 at 23:40
So SELECT AVERAGE((COALESCE( PRICE_INSTORE)+COALESCE(PRICE_PICKUP)+COALESCE(PRICE_DELIVERY)+COALESCE(PRICE_SHIP) ? @JohnnyFitz — Clinton Sorrel
– Clinton Sorrel, Commented Jan 22, 2021 at 23:48

Abelisto · Accepted Answer · 2021-01-23 00:08:54Z

3

select
    *,
    (select avg(x) from unnest(array[price_instore,price_pickup,price_delivery,price_ship]) as x) as avg_price
from
   your_table;

answered Jan 23, 2021 at 0:08

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Organek · Accepted Answer · 2021-01-22 23:38:05Z

2

I'm drunk, so there's probably a much better way to do this than pivoting, but I cannot see it now with the room spinning around me like it is.

with j as (
  select product, to_jsonb(mytable) - 'product' as jdata
    from mytable
)
select j.product, avg(e.value::numeric) as avg_price
  from j
       cross join lateral jsonb_each(j.jdata) as e(key, value)
 where e.value is not null
 group by j.product.

answered Jan 22, 2021 at 23:38

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Comments

Clinton Sorrel · Accepted Answer · 2021-05-11 13:52:12Z

1

Try this

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

edited May 11, 2021 at 13:52

Clinton Sorrel

871 silver badge7 bronze badges

answered Jan 23, 2021 at 3:43

Mukhtiar Ahmed

6314 silver badges13 bronze badges

1 Comment

Abelisto Over a year ago

Do you know that anything = null is always null? dbfiddle.uk/… Should be ... is null

Gordon Linoff · Accepted Answer · 2021-01-22 23:50:19Z

1

One method is a lateral join:

select t.*, avg_price
from t cross join lateral
     (select avg(price) as avg_price
      from (values (price_instore), (price_pickup), (price_delivery), (price_ship)
           ) v(price)
     ) x

answered Jan 22, 2021 at 23:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Johnny Fitz · Accepted Answer · 2021-01-23 00:22:25Z

1

try this:

   select coalesce(PRICE_INSTORE,0) + coalesce(PRICE_PICKUP,0) + coalesce(PRICE_DELIVERY,0) + coalesce(PRICE_SHIP,0)) / 4

answered Jan 23, 2021 at 0:22

Johnny Fitz

5422 silver badges8 bronze badges

2 Comments

Clinton Sorrel Over a year ago

The only issue I see with this one is the hardcoded "4" - I want the average of available prices, so for example if shipping unavailable I'd divide by 3. That's the problem I kept running into with my attempts

Johnny Fitz Over a year ago

@ClintonSorrel as you have probably seen by now you could use a Case statement as described in Mukhtiar's answer above with case when PRICE_DELIVERY is null then 0 else 1 end.

Rajat · Accepted Answer · 2021-01-23 01:01:43Z

1

This one is probably the easiest to digest. Performance shouldn't be too bad unless you have a very large table that is also, not indexed

 with cte (product, prices) as 
  
(select product, price_instore from t union all
 select product, price_pickup from t union all
 select product, price_delivery from t union all
 select product, price_ship from t)
  
select product, avg(prices)
from cte
group by product;

You an either use the output as is, or wrap it around another CTE and use that to join on your original table to get the averages

answered Jan 23, 2021 at 1:01

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

Average Multiple Columns in Postgres

6 Answers 6

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related