CODEWITHSUNDEEP
sqlpostgresql
CodeAgainst
CodeAgainst

Reputation: 153

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

Upvotes: 0

Views: 451

Answers (2)

Ronald
Ronald

Reputation: 2882

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

Upvotes: 0

Edouard
Edouard

Reputation: 7065

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

Upvotes: 1

Related Questions