#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0

    Horizontal counting?


    I would like to run a query that counts by case_manager, number of distinct app_id's that have a status ='AC' in a select number of programs. All of the fields are in the same table.

    I want it to look like this:

    Case Mgr A D YY OY
    Jane 1 3 42 0
    John 3 4 5 2
    Alice 1 2 3 3

    Fields are case_manager, status, applicant, and program
    table reg

    I can do the count command to find the total of all active people for each region code. What I want it for the breakdown by program the people are in.

    My query for that is:
    SELECT case_manager, Count (*)
    from reg
    where status='AC'
    Group by case_manager
    order by case_manager
    and I get this:
    Case Manager Count *
    Jane 46
    John 14
    Alice 9
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    I believe what your looking for is a pivot table, Here are a couple of examples that might work for you:
    Code:
    --This one just uses SQL*Plus:
    variable  case_mgr_ref_cur    refcursor
    declare
      v_sql       varchar2( 32767 );
     begin
       v_sql  :=  'select *
                    from  ( select case_manager, applicant, count(*) as case_manager_count
                             from  schema.table
                            where  status  =  'AC'
                            group  by case_manager, applicant )
                    pivot ( sum( case_manager_count )
                     for applicant in(';
       for r in( select distinct applicant
                  from  schema.table )
       loop
         v_sql  :=  v_sql || '''' ||  r.applicant || ''',';
       end loop;
    
       v_sql  :=  rtrim( v_sql, ',' ) || ' ) )';
       open :case_mgr_ref_cur for v_sql;
     end;
     /
    
    print case_mgr_ref_cur
    
    ------------------------------------------------------------------------------------------------------
    --And if you on at least Oracle 11.2 you can generate the SQL to copy/paste/run with this sql:
    select 'select *
             from  ( select case_manager, applicant, count(*) as case_manager_count
                      from  schema.table
                     where  status  =  'AC'
            group by case_manager, applicant )
            pivot ( sum( case_manager_count ) for applicant in (' ||
            listagg ( '''' || applicant || '''', ',' ) within group ( order by applicant ) || '))'
     from  ( select distinct applicant
              from  schema.table )

IMN logo majestic logo threadwatch logo seochat tools logo