Sum Up values in a query based on other information

oracle

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:

enter image description here

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

enter image description here

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,'20130307085914CS','20130307084811CS','10','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000133',61,13,'20130307090200CS','20130307084811CS','10','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000014',61,4,'20130304140400CS','20130304135945CS','09','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000173',79,1,'20130307161532CS','20130307161424CS','01','17FL012011','T007F12127');
Insert into "mv_outage_duration" (EVENTNUM,NUM_CUST,OUT_MINUTES,RESTORE_DTS,OFF_DTS,CAUSE,FEEDER,DEV_NAME) values ('T00000000173',61,3,'20130307161800CS','20130307161424CS','01','17FL012011','T007F12127');





Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000014',10,796072,'LIGHTNING IN AREA','COMPLETETION REMARKS FROM TRUCK ON TOFS. ');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000014',11,796072,'NORMAL FOR SEASON','COMPLETETION REMARKS FROM TRUCK ON TOFS.');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000173',7,79607,'LIGNTNING IN AREA','wetr');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000173',6,79607,'LIGNTNING IN AREA','wetr');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000133',7,796072,'THUNDERSTORM','Testing Step Restore for Kasey');
Insert into "mv_aeven" (NUM_1,REV_NUM,XPERS,WEATHER_CODE,COMPLETION_REMARKS) values ('T00000000133',6,796072,'THUNDERSTORM','Testing Step Restore for Kasey');

Best Answer

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