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
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