0

table DEPT looks like this.

dname NO_of_emp
Hr 10
Finance 30
Analyst 50

Need output like below.

dname NO_of_emp per_of_emp_dept_wise
Hr 10 11.11
Finance 30 33.33
Analyst 50 55.55

to get 'per_of_emp_dept_wise' column logic is : ((NO_of_emp /total NO_of_emp) *100)

How to achieve this.

tried using rollup:

SELECT
    dname,
    SUM(no_of_emp),
    ( ( no_of_emp ) / SUM(no_of_emp) * 100 )
FROM
    dept
GROUP BY
    ROLLUP(deptno);--> gives error  ORA-00979: not a GROUP BY expression.

but not able to include rollup data into SELECT section to calculate new('per_of_emp_dept_wise') column.

Can any one suggest solution to this.

Thanks in advance.

Rajini

User 15849903 Raj
  • 13
  • 3
  • Does this answer your question? [ORA-00979 not a group by expression](https://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression) – samuei May 06 '21 at 06:59
  • 1
    You can use markdown for codes and table https://stackoverflow.com/editing-help – Jimmy May 06 '21 at 07:02

1 Answers1

1

Try this:

with aux (dname, no_of_emp) as(
    select 'HR', 10 from dual union all
    select 'FINANCE', 30 from dual union all
    select 'ANALYST', 50 from dual)
SELECT
    a.*,
    round(RATIO_TO_REPORT(no_of_emp) OVER() * 100, 2) per_of_emp_dept_wise,
    round(a.no_of_emp / SUM(no_of_emp) OVER() * 100, 2) per_of_emp_dept_wise_2
FROM
    aux a