sql query with avg price per day and group by day

I got this table

+------+------------------+-----------+-----------+
| Name |       Time       | LowPrice  | HighPrice |
+------+------------------+-----------+-----------+
| #AAA | 12/13/2021 17:12 |    383.12 |     393.9 |
| #BBB | 12/13/2021 17:13 |   1110.34 |    1114.1 |
| #AAA | 12/13/2021 17:13 |    384.15 |     399.2 |
| #BBB | 12/13/2021 17:14 |   1112.34 |    1119.1 |
+------+------------------+-----------+-----------+

and this query:

SELECT "Name", "Time", "LowPrice", "HighPrice"
FROM rp_prices
WHERE "Time" > NOW() - INTERVAL '10 day';

I need to get only one price, with avg I think, and grouped by day, something like this

+------+-------------+-----------+-----------+
| Name |    Time     | LowPrice  | HighPrice |
+------+-------------+-----------+-----------+
| #AAA | 12/13/2021  |    383.12 |     393.9 |
| #BBB | 12/13/2021  |   1110.34 |    1114.1 |
+------+-------------+-----------+-----------+

Thanks for your help

Answer

SELECT "Name", date_trunc('day', "Time"), avg("LowPrice"), avg("HighPrice")
  FROM rp_prices
 WHERE "Time" > now() - interval '10 day'
 GROUP BY "Name", date_trunc('day', "Time")

Advertisement

Answer