Add range of values under a column using rowid

oracle

How do i add range of values in a column based on row id?
For example: I have to add all the values under column "time" where rowid>=1 and rowid<=5.

Additionally is it possible to divide the then obtained sum by 3600?

Best Answer

Technically, you could use Oracle's SUM function (good explanation here: http://www.techonthenet.com/oracle/functions/sum.php). Your code would look something like this:

select sum(time)/3600
  from table_name
 where rownum >= 1 and rownum <=5;

However, without knowing more about the table, I don't know if the TIME column is simply an integer, or is a date/timestamp field. If it is date/timestamp, SUM won't really do what you need it to.

One other word of caution, using rownum doesn't always return the same results between runs. Please take a look at http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html for a much more succinct explanation than I can provide.

Related Solutions

SQL set allowed values for a column

alter table ExampleTable
    add (new_column varchar(20) default 'value1',
         constraint ckExampleTable check (new_column in ('value1', 'value2', 'value3')));

Sum Up values in a query based on other information

Here is a solution that uses analytics to prevent having to do two scans of mv_aeven. It does require more CPU to save the disk access and is a bit more complex, so you will have to weigh it accordingly.

SELECT * FROM
(
  SELECT a.eventnum, sum(a.num_cust), sum(a.out_minutes), a.off_dts, max(a.restore_dts)
     , a.cause, a.feeder, a.DEV_NAME
  FROM mv_outage_duration a
  WHERE a.DEV_NAME = 'T007F12127'
  GROUP BY a.eventnum, a.off_dts, a.cause, a.feeder, a.DEV_NAME
) a
INNER JOIN 
  (
  SELECT Num_1, xpers, weather_code, completion_remarks FROM 
     (
     SELECT Num_1, xpers, weather_code, completion_remarks, rev_num
        , max(rev_num) OVER (PARTITION BY Num_1) max_rev
     FROM mv_aeven
     )
  WHERE rev_num = max_rev
  ) d
ON d.Num_1 = a.eventnum;

SQL Fiddle (Includes column type definitions).

Best Answer

Related Solutions

SQL set allowed values for a column

Sum Up values in a query based on other information

Related Question