sorry you need to add date also:
SELECT buildingID,meastime,avg(value)
From measurement
where buildingID = 2
and meastime between to_date('2002-02-24', 'YYYY-MM-DD') AND
to_date('2002-02-27', 'YYYY-MM-DD')
group by buildingID,meastime
order by buildingID,meastime
manjushelar_at_hotmail.com (MS) wrote in message news:<29ed5c29.0204120950.205ef02_at_posting.google.com>...
> Try this
> SELECT buildingID,avg(value)
> From measurement
> where buildingID = 2
> and meastime between to_date('2002-02-24', 'YYYY-MM-DD') AND
> to_date('2002-02-27', 'YYYY-MM-DD')
> group by buildingID
> order by buildingID
>
>
> "XelA" <asilic_at_net.hr> wrote in message news:<a96kkp$ejn$1_at_sunce.iskon.hr>...
> > "Asbjørn Moen" <asbjorn.no.spam.moen_at_ngi.no> wrote in message
> > news:a96k6t$l9m$1_at_snipp.uninett.no...
> > > I have an oracle database with one table named "measturement"
> > >
> > > The table has three fields:
> > > id - number
> > > value - number(10,5)
> > > meastime - date
> > > buildingID - number - an ID that represent a building found in another
> > > table
> > >
> > > In this table, I have for example these values:
> > >
> > > id - value - meastime - buildingID
> > > ---------------------
> > > 1 - 34.55 - 02/24/2002 13:55:50 - 4
> > > 2 - 34.53 - 02/24/2002 16:15:22 - 6
> > > 3 - 34.51 - 02/24/2002 20:24:34 - 2
> > > 4 - 34.54 - 02/25/2002 03:55:51 - 2
> > > 5 - 34.56 - 02/25/2002 13:33:16 - 2
> > > 6 - 34.58 - 02/26/2002 17:52:42 - 5
> > > 7 - 34.63 - 02/27/2002 12:56:44 - 2
> > > 8 - 34.68 - 02/27/2002 04:51:53 - 5
> > > 9 - 34.70 - 02/27/2002 10:04:22 - 6
> > > 10 - 34.74 - 02/27/2002 03:32:52 - 7
> > >
> > > What I want is a list of values that represent the average of each day
> > > represented by it's building id:
> > > for example, I want the list of al data from 02/24/2002 to 02/27/2002 for
> > > buildingID 2. If there is no value in the table for the specified building
> > > the specified day, I want the sql string to output NULL.
> > >
> > > I have discovered that
> > >
> > > SELECT avg(value)
> > > From measurement
> > > where buildingID = 2
> > > and meastime between to_date('2002-02-24', 'YYYY-MM-DD') AND
> > > to_date('2002-02-27', 'YYYY-MM-DD') order by meastime
> > >
> > > displays the results for one building the specified time. But I want it
> > > displayed for ALL buildings in ONE SQL-Query! is this possible via sub
> > > "SELEC" -queries or UNION?
> > >
> >
> > where buildingID = 2 -> Delete that...
Received on Fri Apr 12 2002 - 16:13:21 CDT
