How to calculate the percentage of employee type in OTBI Report?

Hi I have tried a lot of methods using count and sum. An example table can be the following:

I have tried using column formula like this: COUNT(COUNT("Worker"."Person Number") by "Worker"."Assignment Type")100 /COUNT()

But I believe that the first count shouldn’t be there and if I use it without it, OTBI gives me a syntax error. Please help me out with this.

>Solution :

You may try using analytic functions here:

SELECT Name, "Employee Type",
       100.0 * (COUNT(*) OVER (PARTITION BY "Employee Type") /
                COUNT(*) OVER () ) AS Percentage
FROM yourTable
ORDER BY "Employee Type", Name;

Here is a demo for Oracle.

If for some reason you can’t use analytic functions, then a join approach also works here:

SELECT t1.Name, t1."Employee Type",
       100.0 * t2.cnt / (SELECT COUNT(*) FROM yourTable) AS Percentage
FROM yourTable t1
INNER JOIN
(
    SELECT "Employee Type", COUNT(*) AS cnt
    FROM yourTable
    GROUP BY "Employee Type"
) t2
    ON t2."Employee Type" = t1."Employee Type"
ORDER BY "Employee Type", Name;