Three Solutions to Puzzle of the Week #14

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

Solutions

#1: Using Analytic Functions (SUM)

SELECT DISTINCT deptno, 
                ROUND(100*SUM(sal)OVER(PARTITION BY deptno)/SUM(sal)OVER(),2) AS "Share, %"
FROM emp
ORDER BY 1
/

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

#2: Using Cartesian Product

SELECT a.deptno, 
       ROUND(100*SUM(a.sal)*COUNT(DISTINCT a.ROWID)/(SUM(b.sal)*COUNT(DISTINCT b.ROWID)), 2) AS "Share, %"
FROM emp a, emp b
GROUP BY a.deptno
ORDER BY 1
/
DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

#3: Using SUM(DISTINCT ..) on a Cartesian Product

SELECT a.deptno,
       ROUND(100*TRUNC(SUM(DISTINCT a.sal+a.empno/1000000)) /
                 TRUNC(SUM(DISTINCT b.sal+b.empno/1000000)), 2) "Share, %"
FROM emp a, emp b
GROUP BY a.deptno
ORDER BY 1;
DEPTNO   Share, %
------ ----------
    10      30.15
    20      37.47
    30      32.39

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.

Advertisements

One thought on “Three Solutions to Puzzle of the Week #14

  1. Dieter Noeth March 13, 2018 / 2:43 am

    Well, RATIO_TO_REPORT provides this in a single function:

    SELECT deptno,
    ROUND(100*RATIO_TO_REPORT(SUM(sal))OVER(),2) AS “Share, %”
    FROM emp
    GROUP BY deptno
    ORDER BY 1;

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s