How use a few columns using group by in PostgresSQL?

user_7088386 0
Viewed 41

How i can use COALESCE(price)? Because i got error "non aggregated", how i can use all columns in select with group by? COALESCE(price) - mean first value in group, but im need first and last value in group too, but how i can do it?

select MAX(price) as high, MIN(price) as low, COALESCE(price) as open, date_trunc('second', created_at) as secondVal
from "eur_usd_ticks"
where "created_at" > '2020-07-19 23:15:49' and "created_at" <= '2020-07-19 23:49:09'
group by secondVal order by "created_at" desc

Structure: price|created_at

postgresql
1 Answers

coalesce is of no use. try this:

SELECT MAX(price) as high, MIN(price) as low, 
  (ARRAY_AGG(price ORDER BY created_at ASC))[1] AS open_price,
  (ARRAY_AGG(price ORDER BY created_at DESC))[1] as close_price, 
  date_trunc('second', created_at) as secondVal
from "eur_usd_ticks"
where "created_at" > '2020-07-19 23:15:49' and "created_at" <= '2020-07-19 23:49:09'
group by secondVal order by secondVal desc
user_120815430
Related