SQL-question

From: Asbjørn Moen <asbjorn.no.spam.moen_at_ngi.no>
Date: Fri, 12 Apr 2002 14:34:13 +0200
Message-ID: <a96k6t$l9m$1@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?

Regards
Asbjorn

order by meastime Received on Fri Apr 12 2002 - 07:34:13 CDT