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?
[Postgres] Select avg of a days worth of data
Moderator: General Moderators
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: [Postgres] Select avg of a days worth of data
Why doesn't it get the averages quite right? Remember that timestamps are usually saved in GMT.
(#10850)
Re: [Postgres] Select avg of a days worth of data
I think you should
in your first query.
Code: Select all
GROUP BY date_trunc('day',time_stamp)There are 10 types of people in this world, those who understand binary and those who don't
Re: [Postgres] Select avg of a days worth of data
That worked wonders.VladSun wrote:I think you shouldin your first query.Code: Select all
GROUP BY date_trunc('day',time_stamp)
Much better than the sub query.
Re: [Postgres] Select avg of a days worth of data
You should also remove the DISTINCT clause - the GROUP BY clause makes its usage senseless.
There are 10 types of people in this world, those who understand binary and those who don't