4 Replies Latest reply on Sep 4, 2018 11:06 AM by Deepak Rai

    Calculating the sum of a specific field only when certain fields are unique

    David Schwindt

      I have a dataset of public safety call for service records. I am trying to calculate the total number of minutes staff spent with individuals who have frequent contact with law enforcement. I'd like to get the sum of minutes spent for each unique call for service (Dis ID) and each officer on that call (Call For Service Officer Id). For example, if Officer Jones spends 10 minutes on a particular call and Officer Smith spends 15 minutes on the same call, I would like a total of 25 minutes for that call. This works well when a call has no updates, such as the first two columns here.



      I thought this would be straight forward, but our dispatch system creates a new record each time a call is updated and for each staff member assigned to a call. For example, a call for service with 2 staff members and 2 updates by dispatch creates a total of 6 records in the database. That would triple our time spent to 75 minutes. You can see this in the two columns on the right above.


      How can I get a total only when there is a distinct "Dis ID" and "Call For Service Officer Id" combination? This would give me the the correct number of minutes, regardless of how many records there are for a particular officer on a particular call. Or is there a better solution?