SQL & PL/SQL

I am trying to analysis data based on a couple of date fields and determine various metrics from this data. The table in question has many columns, but the only ones pertaining to this query are PROCESS_ID (varchar), START_DT, END_DT. I'm trying to analyze the length of time various processes have ran, which involves calculating the minutes from the two dates: (end_dt-start_dt)*1440.

I am wanting results in a format like:
PROCESS_ID, Cumulative minutes, median daily minutes, avg daily minutes.

However, I can't seem to get my query to work right.

For example, here is my query so far:

SELECT process_id, to_char(start_dt, 'YYYY-MM-DD') start_day, to_char(end_dt, 'YYYY-MM-DD') end_day,
       SUM((end_dt - start_dt) * 1440) over(PARTITION BY process_id, to_char(start_dt, 'YYYYMMDD')) cum_minutes,
       median((end_dt - start_dt) * 1440) over(PARTITION BY process_id, to_char(start_dt, 'YYYYMMDD')) median_daily_minutes,
       AVG((end_dt - start_dt) * 1440) over(PARTITION BY process_id, to_char(start_dt, 'YYYYMMDD')) avg_daily_minutes
  FROM process_table
 ORDER BY process_id, start_dt;

As you can see, I'm trying to get the total number of minutes spent processing a particular process, the medium daily minutes and the average. However, I am getting multiple rows for a given day. Something like this:

PROCESS_ID START_DAY       END_DAY       CUM_MINUTES MEDIAN_DAILY_MINUTES AVG_DAILY_MINUTES
---------- --------------- ------------- ----------- -------------------- -----------------
PROC_A     2011-10-24      2011-10-25         261.55              130.775           130.775
PROC_A     2011-10-25      2011-10-26          236.7               118.35            118.35
PROC_A     2011-10-25      2011-10-26          236.7               118.35            118.35
PROC_A     2011-10-26      2011-10-26         148.05               148.05            148.05
PROC_A     2011-10-27      2011-10-27     92.7333333           92.7333333        92.7333333
PROC_A     2011-10-28      2011-10-28         244.45           90.1333333        81.4833333
PROC_A     2011-10-28      2011-10-28         244.45           90.1333333        81.4833333
PROC_A     2011-10-28      2011-10-28         244.45           90.1333333        81.4833333
PROC_A     2011-10-29      2011-10-29     48.0333333           48.0333333        48.0333333

I tried adding grouping but I get an error, "not a group by expression".

Any advise?

Edited by: nik_m on Nov 16, 2011 9:55 AM