sql query with avg price per day and group by day-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Something like

SELECT "Name", "Time", 
       min("LowPrice") as min_lp, 
       max("HighPrice") as max_hp, 
       avg("LowPrice") as avg_lp,
       avg("HighPrice") as avg_hp,
       avg(("HighPrice" - "LowPrice")/2) as avg_middle 
FROM rp_prices
where "Time" > now() - interval '10 day'
group by "Name", "Time";

might give you the answer you are looking for. It is allowed to use functions in the Group By clause. You can use those if the column "Time" has a higher precision than days