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,'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.
SQL Fiddle (Includes column type definitions).