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```