How to group and sum 1

[scooterboygnu]

HI i am running the below script but would like to sum up the amount of seconds for each day to shorten the results:

script is:
SELECT NOP_ID||','||FRN_ID||','||IN_PRODUCT||','||OUT_PRODUCT||','||CALL_START_DATE||','||DURATION_IN_SECONDS||','||ERROR_VALUE FROM ERROR_CDR
where fk_bar_run_number >= &first_batch
and fk_bar_run_number <= &Last_batch
and fk_ert_id = 'CNP NF'
and nop_id = '31'
and IN_PRODUCT = 'IPL'

results appear as:
31,TEC,IPL,20070831,4
31,TEC,IPL,20070831,6
31,TEC,IPL,20070830,4
31,TEC,IPL,20070830,5

would like this to show as:

31,TEC,IPL,20070831,10
31,TEC,IPL,20070830,9

basically sum up the seconds and group by day.

Many thanks

 

[lewisp]

I'm not on version 8 here so I can't confirm if this will work, but try this:

SELECT NOP_ID ||','||
       FRN_ID ||','||
       IN_PRODUCT ||','||
       OUT_PRODUCT ||','||
       CALL_START_DATE ||','||
       DURATION ||','||
       ERROR_VALUE
  FROM (
  SELECT NOP_ID,
         FRN_ID,
         IN_PRODUCT,
         OUT_PRODUCT,
         CALL_START_DATE,
         SUM(DURATION_IN_SECONDS) duration,
         ERROR_VALUE
  FROM   ERROR_CDR
  where fk_bar_run_number >= &first_batch
  and fk_bar_run_number <= &Last_batch
  and fk_ert_id = 'CNP NF'
  and nop_id = '31'
  and IN_PRODUCT = 'IPL'
  GROUP BY NOP_ID,
         FRN_ID,
         IN_PRODUCT,
         OUT_PRODUCT,
         CALL_START_DATE,
         ERROR_VALUE )