CODEWITHSUNDEEP
sqloracle-database
Superdooperhero
Superdooperhero

Reputation: 8096

Oracle sql window functions - total on same row as results

I would like to show the total (labour turnover) and results on the same line using a window function, but I can't get it to work. It gives me "from keyword not found where expected" on the over clause in the total_labour_turnover column.

Basically I would like the total labour turnover to be displayed along with the individual rows' labour turnover. Note that I do not want a running total.

Anyone know how to do this?

select active.cost_centre,
       active.flexi_perm,
       active.active_count,
       nvl(term.term_count, 0) term_count,
       round(nvl(term.term_count, 0) / active.active_count * 100, 2) labour_turnover,
       round(sum(nvl(term.term_count, 0)) / sum(active.active_count) * 100, 2) over (order by 1) total_labour_turnover
from   (       
          select haou.name cost_centre,
                 decode(payr.attribute2, 'F', 'Flexi', 'Perm') flexi_perm,
                 count(paaf.assignment_id) active_count
          from   per_periods_of_service      ppos,
                 per_all_assignments_f       paaf,
                 hr_organization_information hoi,
                 hr_all_organization_units   haou,
                 pay_all_payrolls_f          payr
          where  trunc(:active_count_date) between ppos.date_start and nvl(ppos.actual_termination_date, to_date('31/12/4712', 'dd/mm/yyyy'))
          and    paaf.period_of_service_id = ppos.period_of_service_id
          and    paaf.primary_flag = 'Y'
          and    trunc(:active_count_date) between paaf.effective_start_date and paaf.effective_end_date
          and    hoi.org_information_context = 'TRU_ADD_ORG'
          and    hoi.organization_id = paaf.organization_id
          and    haou.organization_id = paaf.organization_id
          and    payr.payroll_id = paaf.payroll_id
          and    payr.attribute2 in ('F', 'N')  -- Flexi and Non-Flexi
          and    trunc(:active_count_date) between payr.effective_start_date and payr.effective_end_date          
          group  by haou.name,
                 decode(payr.attribute2, 'F', 'Flexi', 'Perm')
       )  active,
       (              
          select haou.name cost_centre,
                 decode(payr.attribute2, 'F', 'Flexi', 'Perm') flexi_perm,
                 count(distinct paaf.person_id) term_count
          from   per_periods_of_service ppos,
                 per_all_assignments_f       paaf,
                 hr_organization_information hoi,
                 hr_all_organization_units   haou,
                 pay_all_payrolls_f          payr          
          where  nvl(ppos.actual_termination_date, to_date('31/12/4712', 'dd/mm/yyyy')) between trunc(:term_start) and trunc(:term_end)
          and    paaf.period_of_service_id = ppos.period_of_service_id
          and    paaf.primary_flag = 'Y'
          and    nvl(ppos.actual_termination_date, to_date('31/12/4712', 'dd/mm/yyyy')) between paaf.effective_start_date and paaf.effective_end_date
          and    hoi.org_information_context = 'TRU_ADD_ORG'
          and    hoi.organization_id = paaf.organization_id
          and    haou.organization_id = paaf.organization_id
          and    payr.payroll_id = paaf.payroll_id
          and    payr.attribute2 in ('F', 'N')  -- Flexi and Non-Flexi
          and    nvl(ppos.actual_termination_date, to_date('31/12/4712', 'dd/mm/yyyy')) between payr.effective_start_date and payr.effective_end_date          
          group  by haou.name,
                 decode(payr.attribute2, 'F', 'Flexi', 'Perm')                 
       )  term
where  term.cost_centre (+) = active.cost_centre
and    term.flexi_perm (+) = active.flexi_perm

Upvotes: 0

Views: 79

Answers (2)

DirkNM
DirkNM

Reputation: 2664

Please try the following, it should help:

round(sum(nvl(term.term_count, 0)) over() / sum(active.active_count) over() * 100, 2) total_labour_turnover

You have two SUM functions in your expression a both of them need be a analytic function.

Upvotes: 1

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

According to Oracle documentation on analytic functions, the order by clause automatically implies a running window of rows between unbounded preceding and current row.

If you don't want a running sum, but a total sum, delete the order by 1 from the over clause of your analytical sum() function and put a partition by null there instead. Or try it with empty parentheses, might work, too.

Upvotes: 0

Related Questions