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 - 12:50:11 CDT
