CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Clinton Sorrel
Clinton Sorrel

Reputation: 87

Average Multiple Columns in Postgres

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

Upvotes: 1

Views: 2737

Answers (6)

Mukhtiar Ahmed
Mukhtiar Ahmed

Reputation: 621

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

Upvotes: 1

Rajat
Rajat

Reputation: 5803

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

Upvotes: 1

Johnny Fitz
Johnny Fitz

Reputation: 542

try this:

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

Upvotes: 1

Abelisto
Abelisto

Reputation: 15614

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12494

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.

Upvotes: 2

Related Questions