5 Replies Latest reply: Mar 17, 2014 7:06 AM by _Karthick_ RSS

    how to find cummulative count in oracle sql

    984148

      Hi,

       

      Greetings!!!

       

      I am using oracle 11g with plsql developer.

       

      I have the data set as below and I wanted to find the cummulative count of closed errors at monthly level.

       

      COUNTRYSTATE_IDERROR_IDERROR_STATUSCREATED_DATECLOSE_DATE
      AUS10515312809152Closed5/22/2013
       
      5/31/2013
       
      AUS10515312810652Closed5/22/2013
       
      5/31/2013
       
      AUS10515321160252Closed5/23/2013
       
      5/31/2013
       
      AUS10515321158552Closed5/23/2013
       
      5/31/2013
       
      AUS10515327843952Closed5/24/2013
       
      5/31/2013
       
      AUS10515367737152Closed5/30/2013
       
      6/17/2013
       
      AUS10515367736552Closed5/30/2013
       
      6/6/2013
       
      AUS10515367737052Closed5/30/20136/17/2013
      AUS10515367736952Closed5/30/20136/6/2013
      AUS10515367736652Closed5/30/20136/17/2013
      AUS10515367736752Closed5/30/20136/17/2013
      AUS10515367736852Closed5/30/20138/6/2013
       
      AUS10515367737552Closed5/30/20138/8/2013
       
      AUS10515367737652Closed5/30/20138/8/2013
      AUS10515389124052Closed5/31/20138/4/2013
      AUS10515455756552Closed6/7/20138/19/2013
      AUS10515455756652Closed6/7/20138/19/2013
      AUS10515471563752Closed6/8/20138/13/2013

       

      I have tried with the following query to get the cummulative count.  The problem with this query is if any month , the error is not closed it is not showing the previous value . it is just giving the blank value. Could you please help me while doing the cummulative count how to ensure if the particular month no entry is there but we have to have previous month value.

       

      select a.country_code,
             a.study_site_id,
             a.created_month,
             a.created_year,
             sum(b.closed_issues) over(partition by
        b.country_code, b.state_id ORDER BY b.country_code, b.state_id,
        b.closed_year, b.closed_month) as cumm_closed_error
        from (select country_code,
                     STATE_id,
                     to_char(close_date, 'mon')
        closed_month,
                     to_char(close_date, 'yyyy')
        closed_year,
                     count(ERROR_ID) closed_errors
                from abc
               group by country_code,
                        STATE_ID,
                        to_char(close_date, 'mon'),
                        to_char(close_date,
        'yyyy')) count_error
      order by country_code,
                state_id,
                to_char(close_date, 'yyyy'),
                to_char(close_date, 'mon')

       

      Thanks in advance

        • 1. Re: how to find cummulative count in oracle sql
          AnnPricks E

          Can you please provide create table, insert script and required output from that?

          • 2. Re: how to find cummulative count in oracle sql
            _Karthick_

            Show the expected output. And the query posted by you has syntax errors. Please read Re: 2. How do I ask a question on the forums?

            • 3. Re: how to find cummulative count in oracle sql
              Partha Sarathy S

              Not sure you are looking for this. Check this.

               

              SELECT COUNTRY,STATE_ID,ERROR_ID,ERROR_STATUS,CREATED_DATE,CLOSE_DATE,

                     COUNT(*)OVER(PARTITION BY COUNTRY,STATE_ID ORDER BY CREATED_DATE,CLOSE_DATE) SUM_AMT

              FROM ABC;

               

              OUTPUT:

              AUS 1051 5312809152 Closed 22-MAY-13 31-MAY-13 2

              AUS 1051 5312810652 Closed 22-MAY-13 31-MAY-13 2

              AUS 1051 5321160252 Closed 23-MAY-13 31-MAY-13 4

              AUS 1051 5321158552 Closed 23-MAY-13 31-MAY-13 4

              AUS 1051 5327843952 Closed 24-MAY-13 31-MAY-13 5

              AUS 1051 5367736952 Closed 30-MAY-13 06-JUN-13 7

              AUS 1051 5367736552 Closed 30-MAY-13 06-JUN-13 7

              AUS 1051 5367737052 Closed 30-MAY-13 17-JUN-13 11

              AUS 1051 5367737152 Closed 30-MAY-13 17-JUN-13 11

              AUS 1051 5367736652 Closed 30-MAY-13 17-JUN-13 11

              AUS 1051 5367736752 Closed 30-MAY-13 17-JUN-13 11

              AUS 1051 5367736852 Closed 30-MAY-13 06-AUG-13 12

              AUS 1051 5367737552 Closed 30-MAY-13 08-AUG-13 14

              AUS 1051 5367737652 Closed 30-MAY-13 08-AUG-13 14

              AUS 1051 5389124052 Closed 31-MAY-13 04-AUG-13 15

              AUS 1051 5455756552 Closed 07-JUN-13 19-AUG-13 17

              AUS 1051 5455756652 Closed 07-JUN-13 19-AUG-13 17

              AUS 1051 5471563752 Closed 08-JUN-13 13-AUG-13 18

              If this is not, then as others said post required output

              • 4. Re: how to find cummulative count in oracle sql
                K.S.I.

                In yours there are errors and it won't work as there is no compliance of request and the shown data...

                If it is necessary to count quantity of errors in a month with 'Closed' status, then

                 

                with abc as

                (

                select

                         'AUS' COUNTRY, 1051  STATE_ID,  5312809152    ERROR_ID    , 'Closed' ERROR_STATUS    ,    '22/5/2013' CREATED_DATE   ,'31/5/2013' CLOSE_DATE

                from

                         dual union all

                select

                         'AUS',1051    ,5312810652,'Closed','22/5/2013','31/5/2013'

                from dual

                 

                )

                select

                       a.*,

                       count(case when ERROR_STATUS    = 'Closed' then ERROR_ID end) over (partition by country,STATE_ID,trunc(to_date(created_date,'dd-mm-yyyy'),'mm'))  closed_errors

                from abc  a

                • 5. Re: how to find cummulative count in oracle sql
                  _Karthick_

                  This is what i came up with.

                   

                  SQL> select *
                    2    from k;

                   

                  COU   STATE_ID   ERROR_ID ERROR_ CREATED_D CLOSED_DA
                  --- ---------- ---------- ------ --------- ---------
                  AUS       1051 5312809152 Closed 22-MAY-13 31-MAY-13
                  AUS       1051 5312810652 Closed 22-MAY-13 31-MAY-13
                  AUS       1051 5321160252 Closed 23-MAY-13 31-MAY-13
                  AUS       1051 5321158552 Closed 23-MAY-13 31-MAY-13
                  AUS       1051 5327843952 Closed 24-MAY-13 31-MAY-13
                  AUS       1051 5367737152 Closed 30-MAY-13 17-JUN-13
                  AUS       1051 5367736552 Closed 30-MAY-13 06-JUN-13
                  AUS       1051 5367737052 Closed 30-MAY-13 17-JUN-13
                  AUS       1051 5367736952 Closed 30-MAY-13 06-JUN-13
                  AUS       1051 5367736652 Closed 30-MAY-13 17-JUN-13
                  AUS       1051 5367736752 Closed 30-MAY-13 17-JUN-13
                  AUS       1051 5367736852 Closed 30-MAY-13 06-AUG-13
                  AUS       1051 5367737552 Closed 30-MAY-13 08-AUG-13
                  AUS       1051 5367737652 Closed 30-MAY-13 08-AUG-13
                  AUS       1051 5389124052 Closed 31-MAY-13 04-AUG-13
                  AUS       1051 5455756552 Closed 07-JUN-13 19-AUG-13
                  AUS       1051 5455756652 Closed 07-JUN-13 19-AUG-13
                  AUS       1051 5471563752 Closed 08-JUN-13 13-AUG-13

                   

                  18 rows selected.

                   

                  SQL>  with temp
                    2  as
                    3  (
                    4  select country
                    5        , state_id
                    6        , error_id
                    7        , case when error_month < extract(month from closed_date) then
                    8                          'Open'
                    9              else
                  10                          error_status
                  11          end error_status
                  12        , created_date
                  13        , case when error_month < extract(month from closed_date) then
                  14                          null
                  15              else
                  16                          closed_date
                  17          end closed_date
                  18        , error_month
                  19        , error_year
                  20    from (
                  21                select country
                  22                      , state_id
                  23                      , error_id
                  24                      , error_status
                  25                      , created_date
                  26                      , closed_date
                  27                      , extract(month from add_months(created_date, level-1)) error_month
                  28                      , extract(year  from add_months(created_date, level-1)) error_year
                  29                  from k
                  30                connect
                  31                    by level <= extract(month from closed_date) - extract(month from created_date) + 1
                  32                    and prior country  = country
                  33                    and prior state_id = state_id
                  34                    and prior error_id = error_id
                  35                    and prior dbms_random.value is not null
                  36          )
                  37  )
                  38  select error_year
                  39      , error_month
                  40      , country
                  41      , state_id
                  42      , closed_error_count
                  43      , sum(closed_error_count) over(order by error_month) closed_error_count_cumm
                  44    from (
                  45            select error_year
                  46                , error_month
                  47                , country
                  48                , state_id
                  49                , count(case when closed_date is not null then 1 end) closed_error_count
                  50              from temp
                  51            group
                  52                by error_year
                  53                , error_month
                  54                , country
                  55                , state_id
                  56        );

                   

                  ERROR_YEAR ERROR_MONTH COU  STATE_ID CLOSED_ERROR_COUNT CLOSED_ERROR_COUNT_CUMM
                  ---------- ----------- --- ---------- ------------------ ----------------------
                        2013          5 AUS      1051                  5                        5
                        2013          6 AUS      1051                  6                       11
                        2013          7 AUS      1051                  0                       11
                        2013          8 AUS      1051                  7                       18

                   

                  SQL>