[Postgres] Select avg of a days worth of data
Posted: Mon Jul 12, 2010 10:57 pm
Im new to PGSQL however im fairly used to MySQL.
I am trying to create a query to return the following result set:
[sql]
day | avg(valuePerDay)
13 | 22.2
14 | 23.2
15 | 22.6
[/sql]
The data in the database:
[sql]`id`,`timestamp`, `value`
('1','2010-07-13 1:32:26+00','22.3')[/sql]
Basically i am trying to group an average of `value` for a single day using date_trunc('day',timestamp)
This is my initial query:
[sql]select distinct date_trunc('day',time_stamp) as time_stamp, avg(value) as value from data WHERE id = 1 group by time_stamp order by time_stamp asc[/sql]
However it doesnt seem to get the average of the entire days worth, so i changed it to:
[sql]select distinct date_trunc('day',time_stamp) as time_day, (select avg(value) from data where date_trunc('day',time_stamp) = time_day) as value from data where id = 1 group by time_day order by time_day asc[/sql]
Now im receiving the error:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "poll_day" does not exist LINE 1: ... sensor_data where date_trunc('day',poll_stamp) = poll_day)
Is there any way to make this query smaller or get it working properly without a subquery?
I am trying to create a query to return the following result set:
[sql]
day | avg(valuePerDay)
13 | 22.2
14 | 23.2
15 | 22.6
[/sql]
The data in the database:
[sql]`id`,`timestamp`, `value`
('1','2010-07-13 1:32:26+00','22.3')[/sql]
Basically i am trying to group an average of `value` for a single day using date_trunc('day',timestamp)
This is my initial query:
[sql]select distinct date_trunc('day',time_stamp) as time_stamp, avg(value) as value from data WHERE id = 1 group by time_stamp order by time_stamp asc[/sql]
However it doesnt seem to get the average of the entire days worth, so i changed it to:
[sql]select distinct date_trunc('day',time_stamp) as time_day, (select avg(value) from data where date_trunc('day',time_stamp) = time_day) as value from data where id = 1 group by time_day order by time_day asc[/sql]
Now im receiving the error:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "poll_day" does not exist LINE 1: ... sensor_data where date_trunc('day',poll_stamp) = poll_day)
Is there any way to make this query smaller or get it working properly without a subquery?