Please note this is about day 5 for me and SQL.
I?m having problems getting my arms around SQL and how to capture and
process results from data retrieved. I thought a real world problem
might help tune my thinking.
A good answer would be to help with good solid code with explanations
on why it works.

I?m trying to compare yesterday?s hourly CPU utilization to an average
of the last six months utilization of the same hour. This would be
about 15 lines of PERL. Shouldn?t this be easier with a database? But
anyway -

My formula:
Compare YesterdaysHour to (LastSixMonthsAvgHour+100%)/Number of CPUs))
I?m looking to flag this server if more than 12 (could go up or down)
of the Yesterdays are higher than its corresponding SixMonthsAvg.

I?m having success retrieving the data ? I am trying to shorten my
learning curve on Joins, ?nested selects?, identifier math ,etc.


The following works ? I get a nice list of 24 hourly data points
averaged for the last (30*6) days converted to percent + 100% per my
formula. I average for the hour as the data is in approximately 15 min
intervals.

-- Average of last 6 months of Hourly Data
SELECT 
to_char(METRICS.TIME, 'HH24') as hourofday6,
((avg(METRICS.CPU_UTILIZATION)/100)+1) as avgcpu6,
to_char(sysdate-30*6,'yyyy/mm/dd') as daysago6
FROM ALLDATA.METRICS METRICS
WHERE  (METRICS.HOST_ID='thehostname') AND (to_char(METRICS.TIME,
'yyyy/mm/dd') > to_char(sysdate-(30*6), 'yyyy/mm/dd'))
group by to_char(METRICS.TIME, 'HH24')
order by to_char(METRICS.TIME, 'HH24');

This Also works ? I get a list of hourly data points averaged for
Yesterday (actually the data is a day late)  I also covert to percent.

-- Average of "Yesterday's" Hourly Data
SELECT 
to_char(METRICS.TIME, 'HH24') as hourofday,
(avg(METRICS.CPU_UTILIZATION)/100) as avgcpu,
to_char(sysdate-2,'yyyy/mm/dd') as daysago
FROM ALLDATA.METRICS METRICS
WHERE  (METRICS.HOST_ID='thehostname') AND (to_char(METRICS.TIME,
'yyyy/mm/dd') > to_char(sysdate-2, 'yyyy/mm/dd'))
group by to_char(METRICS.TIME, 'HH24')
order by to_char(METRICS.TIME, 'HH24');

These two assure the number of processors haven?t changed
-- Get number of processors from "max" record of summdata
SELECT min(DAILYSTAT.NUMBER_PROCESSORS) as NUMPMin
FROM ALLDATA.DAILYSTAT DAILYSTAT
WHERE  (DAILYSTAT.HOST_ID='thehostname');

-- Get number of processors from "min" record of summdata
SELECT max(DAILYSTAT.NUMBER_PROCESSORS) as NUMPMax
FROM ALLDATA.DAILYSTAT DAILYSTAT
WHERE  (DAILYSTAT.HOST_ID='thehostname');

SO ? FROM TOAD ? MY RESULTS ( TRUNCATED )

HO    AVGCPU6 DAYSAGO6  
-- ---------- ----------
00 1.37672379 2005/09/22
01 1.50538883 2005/09/22
02 1.48663612 2005/09/22 . . . .
24 rows selected.

HO     AVGCPU DAYSAGO   
-- ---------- ----------
00      .4133 2006/03/19
01     .44625 2006/03/19
02    .439525 2006/03/19. . . .
24 rows selected.

   NUMPMIN
----------
         4
   NUMPMAX
----------
         4

Hour 00: .4133 > (1.37 / 4 ) -  BING!
Hour 01: .4462 > (1.50 / 4 ) -  BING!
Hour 02: .4395 > (1.48 / 4 ) -  BING!

Count for ?thehostname? stands at 3. This server is on its way to being flagged.


CONSTRANTS:
Oracle 9x Database
I am read only ? I cannot create Views or Temp Tables, etc. So I must
do in memory/code.
About 5000 ?thehostnames? to process.
Working to develop Crystal Reports for final solution.