Assuming you are using at least Oracle 8 – you could use an inline view to pre calculate the summary level data and then join this to the detail records. I have tried to rewrite your query so:
sum(case when view_work_summary.wrks_authorized = ‘N’ then 1 else 0 end) as totua
view_work_summary.emp_id = employee_team.emp_id
and employee_team.wbt_id = workbrain_team.wbt_id
and workbrain_team.wbt_id = sec_wb_team_tree.wbt_id
and view_work_summary.wrks_work_date >= (TO_DATE(SUBSTR(‘#dpStartDate.value#’,1,8),’yyyymmdd’) )
and view_work_summary.wrks_work_date < (TO_DATE(SUBSTR(‘#dpEndDate.value#’,1,8),’yyyymmdd’) + (1))
and sec_wb_team_tree.wbt_parent_id = #dbTeam.valueId#
group by workbrain_team.wbt_name,
my_view_summary.wrks_id = view_work_detail.wrks_id
GROUP BY my_view_summary.wbt_name
Note that the calculation of totua is done inside the inline view(a virtual table) and you then join this to the view_work_detail records.
Also did you know that you can easily strip the time portion from a date filed by using the TRUNC function? I see you are using substr – just do TRUNC(dpStartDate.value) and this will strip off the time.
Hope some of this helps