Puzzle of the Week #14: Find department shares in total Salary

Puzzle of the Week #14:

For each department, find its share in the company’s total payroll. The puzzle should be solved with a single SELECT statement that does not utilize sub-queries, WITH clause, in-line views, temporary tables or PL/SQL functions

Expected Result:

DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39


To submit your answer (one or more!) please start following this blog and add a comment to this post.

A correct answer (and workarounds!) will be published here in a week.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.


3 thoughts on “Puzzle of the Week #14: Find department shares in total Salary

  1. sajith June 6, 2016 / 11:56 pm

    SELECT distinct
    round(sum(sal) OVER (partition by deptno)/sum(sal) OVER () *(100),2) result
    FROM emp
    order by deptno

    • Zahar Hilkevich June 7, 2016 / 3:07 pm

      Good. Can you do it without Analytic functions as well?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s