"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 - 07:40:57 CDT
