## 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.