Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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');
share|improve this question
1  
It would be helpful if you have us some mocked-up data. Like a table structure and a couple of INSERT statements. Then we don't all have to spend time doing that just to answer your question. Also, the image you embedded is very hard to see - you'd be better off inserting a text table of your results. –  Max Vernon Mar 12 '13 at 19:23
    
I adjusted the images, but am not sure how to make the insert statements. –  Kasey Mar 12 '13 at 19:58
    
much better on the images. I can see them now! I'll be back about the insert statements. –  Max Vernon Mar 12 '13 at 20:05
2  
Have a look at this question for the INSERT statements. dba.stackexchange.com/questions/14631/… Where he says "so the table is filled accordingly" - its the 2nd bit of code. –  Max Vernon Mar 12 '13 at 20:11
1  
That information has been added. –  Kasey Mar 12 '13 at 20:52
show 4 more comments

3 Answers

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

share|improve this answer
add comment

To get the total, you need to put the num_cust and out_minutes within a sum and exclude these from the group by. This will result in you having just one row for each eventnum, xpers pair.

Also, you don't need a distinct clause when you have a group by - the grouping will remove the duplicates for you. If you're getting duplicate values you weren't expecting, it's because your group by columns are wrong.

This gives you a query looking like this:

 select a.eventnum, sum(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, d.xpers
share|improve this answer
    
This is close, I guess I should have laid out all of the data, as it is causing me duplication errors when I run it against my dataset. I updated the inserts and creates to mimick my dataset. –  Kasey Mar 12 '13 at 21:33
add comment

After I added all my data, I realized that the restore_dts was causing my sql to return each line based on restore time. I only needed the max of it, so I rewrote the sql to this:

select a.eventnum, sum(a.num_cust), sum(a.out_minutes), d.xpers, a.off_dts, max(a.restore_dts), a.cause, a.feeder, d.DEV_NAME, d.weather_code, d.completion_remarks
 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, d.xpers, a.off_dts, a.cause, a.feeder, d.DEV_NAME, d.weather_code, d.completion_remarks

Which returns the values in the format I need.

share|improve this answer
    
For this to work, d.DEV_NAME has to be changes to a.DEV_NAME as mv_aeven does not contain a column with that name in your definition. –  Leigh Riffel Mar 13 '13 at 13:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.