Im trying to show how many users are each level versus how many total users have completed a level. I need the Unique User column to be a subtraction of two columns except at the Appr level I just need it to be a repeat of the user column because it's the highest level,

select 
cpnt.cpnt_id 
,count ( distinct pc.stud_id ) users
from 
pa_stud_program sp,
        pa_program p,
        pa_student s,
        pa_stud_cpnt pc,
        ps_program_type pt,
        pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
    and pc.compl_dte is not null
    and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
    and s.jp_id in ('1801','1805','1810','1811')
    and s.EMP_STAT_ID = 'Active'
    AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
    AND cpnt.CPNT_ID         = p.CPNT_ID
    AND cpnt.REV_DTE         = p.REV_DTE
    AND pc.STUD_ID           = sp.STUD_ID
    AND sp.stud_id           = s.STUD_ID
    AND pc.CPNT_ID           = sp.CPNT_ID
    AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
    AND pc.REV_DTE           = sp.REV_DTE
    AND pc.seq_num           = sp.seq_num
    AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE   
    /** and s.PERSON_ID_EXTERNAL  in [UserSearch]*/ 
group by cpnt.cpnt_id
order by users desc

CodePudding user response：

Looks like a candidate for the lag analytic function.

SQL> with test as
  2    -- sample data
  3    (select 'Trainee'  cpnt_id, 44662 users from dual union all
  4     select 'SvcTech'         , 33099       from dual union all
  5     select 'CrewChief'       , 20679       from dual union all
  6     select 'SvcCoord'        , 15600       from dual union all
  7     select 'Appr'            ,  9590       from dual
  8    )
  9  -- subquery return "previous" CPNT_ID value which is then concatenated to its "pair"
 10  -- joined on USERS value (as there's no other, at least not in your example)
 11  select a.cpnt_id,
 12         a.users,
 13         a.cpnt_id || case when b.l_cpnt_id is not null then ' - '|| b.l_cpnt_id end
 14           unique_users
 15  from test a join (select cpnt_id,
 16                           users,
 17                           lag(cpnt_id) over (order by users) l_cpnt_id
 18                    from test
 19                   ) b on a.users = b.users
 20  order by a.users desc;

CPNT_ID        USERS UNIQUE_USERS
--------- ---------- ---------------------
Trainee        44662 Trainee - SvcTech
SvcTech        33099 SvcTech - CrewChief
CrewChief      20679 CrewChief - SvcCoord
SvcCoord       15600 SvcCoord - Appr
Appr            9590 Appr

SQL>

[EDIT, after you changed the requirement]

That's even simpler:

<snip>
 11  select a.cpnt_id,
 12         a.users,
 13         a.users -  nvl(b.l_users, 0) as unique_users
 14  from test a join (select cpnt_id,
 15                           users,
 16                           lag(users) over (order by users) l_users
 17                    from test
 18                   ) b on a.users = b.users
 19  order by a.users desc;

CPNT_ID        USERS UNIQUE_USERS
--------- ---------- ------------
Trainee        44662        11563
SvcTech        33099        12420
CrewChief      20679         5079
SvcCoord       15600         6010
Appr            9590         9590

SQL>

Implementing that into your query: use a CTE:

with your_query as
  (select 
        cpnt.cpnt_id 
        ,count ( distinct pc.stud_id ) users
   from 
        pa_stud_program sp,
        pa_program p,
        pa_student s,
        pa_stud_cpnt pc,
        ps_program_type pt,
        pa_cpnt cpnt
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
    and pc.compl_dte is not null
    and cpnt.cpnt_id in ('Trainee','SvcTech','CrewChief','SvcCoord','Appr')
    and s.jp_id in ('1801','1805','1810','1811')
    and s.EMP_STAT_ID = 'Active'
    AND cpnt.CPNT_TYP_ID     = p.CPNT_TYP_ID
    AND cpnt.CPNT_ID         = p.CPNT_ID
    AND cpnt.REV_DTE         = p.REV_DTE
    AND pc.STUD_ID           = sp.STUD_ID
    AND sp.stud_id           = s.STUD_ID
    AND pc.CPNT_ID           = sp.CPNT_ID
    AND pc.CPNT_TYP_ID       = sp.CPNT_TYP_ID
    AND pc.REV_DTE           = sp.REV_DTE
    AND pc.seq_num           = sp.seq_num
    AND pt.PROGRAM_TYPE_ID   = p.PROGRAM_TYPE   
group by cpnt.cpnt_id
)
select a.cpnt_id,
       a.users,
       a.users -  nvl(b.l_users, 0) as unique_users
from your_query a join (select cpnt_id,
                         users,
                         lag(users) over (order by users) l_users
                  from your_query
                 ) b on a.users = b.users
order by a.users desc;