Sum Up values in a query based on other information

I am trying to grab the sum of 2 columns if 1 column is the same. I currently have a record-set that looks like this:

I get these results by running this statement:

select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

How do I get the sum of Num_cust and Out_Minutes for the record if the eventnum is the same?

I'd like to return 1 and only 1 row for each event number, and if there are more than 1 step, I'd like to add the Num_cust and Out_minutes for each step.

I've tried

select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers, sum(a.Num_cust)
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

and it just returns the results as a new column sum(a.num_cust).

I also tried

select distinct a.eventnum, a.num_cust, a.out_minutes, d.xpers, sum(select Num_cust from mv_outage_duration a where a.eventnum = d.num_1)
 FROM mv_outage_duration a
 INNER JOIN mv_aeven d
 ON d.Num_1 = a.eventnum
 and (d.DEV_NAME = 'T007F12127')
 and d.rev_num = (select max(rev_num)
 from mv_aeven d
 where a.eventnum = d.Num_1)
group by a.eventnum, a.num_cust, a.out_minutes, d.xpers

...but that just wouldn't run at all.

Here's some statements to set everything up

```
Create table mv_outage_duration( eventnum, num_cust, out_minutes, restore_dts, off_dts, cause, feeder, dev_name)
create table mv_aeven (Num_1, rev_num, xpers, weather_code, completion_remarks)

Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000133',79,11,

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).

StackExchange Database Administrators Q#36517, answer score: 2