## Interview Question: For each department count the number of employees who get no commission.

Interview Question:

Write a single SELECT statement that returns  the number of employees who get no commission broken down by department. (Use scott.emp table)

Level:

Intermediate

Expected Result:

DEPTNO NO_COMM_COUNT
10 3
20 5
30 3

## Solutions

A very typical attempt to solve this problem results in the following query:

```SELECT deptno, COUNT(*) no_comm_count
FROM scott.emp
WHERE comm IS NULL OR comm=0
GROUP BY deptno
ORDER BY 1```

Yes, the result looks correct, but is the query correct?

The answer is NO! It would become apparent if we had a department where all employees get paid commission, so the number of those who does not would be 0.

Let’s change the requirement a bit – we will show all department and number of employees hired on Friday:

```SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
ORDER BY 1```

The result of this query is clearly not what we want:

DEPTNO FRI_COUNT
30 2

We would expect the following instead:

DEPTNO FRI_COUNT
10 0
20 0
30 2

Why don’t we get the departments 10 and 20? The answer is very simple – because we filter “all” those department rows with our WHERE clause. So how should we work around?

Let’s start with more intuitive but less efficient approaches – we will use the same query as before and UNION it with another query that returns “empty” departments. Essentially, the original problem transforms into a new one – find all department where no employees were hired on Friday.

Strategy #1: Using UNION ALL with multi-column non-correlated subquery:

```SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0 fri_count
FROM scott.emp
WHERE (deptno, 'FRI') NOT IN (SELECT deptno, TO_CHAR(hiredate, 'DY')
FROM scott.emp)
GROUP BY deptno
ORDER BY 1```
DEPTNO FRI_COUNT
10 0
20 0
30 2

Strategy #2: Using UNION ALL with ALL predicate on correlated subquery:

```SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY')='FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0 no_comm_count
FROM scott.emp a
WHERE 'FRI'!=ALL(SELECT TO_CHAR(hiredate, 'DY')
FROM scott.emp b
WHERE a.deptno=b.deptno)
GROUP BY deptno
ORDER BY 1```

It is apparent that the ALL predicate ensures that no employees were hired on Friday.

Now we will mimic the behavior of the UNION ALL operator using LEFT JOIN:

Strategy #3: Using LEFT JOIN:

```SELECT a.deptno, COUNT(DISTINCT b.empno) fri_count
FROM scott.emp a LEFT JOIN scott.emp b ON a.deptno=b.deptno
AND TO_CHAR(b.hiredate, 'DY')='FRI'
GROUP BY a.deptno
ORDER BY 1```

COUNT(DISTINCT …) is needed to handle a Cartesian Product as the join by deptno column produces many to many  relationship, i.e. Cartesian product.

```WITH e AS (
SELECT deptno, COUNT(*) fri_count
FROM scott.emp
WHERE TO_CHAR(hiredate, 'DY') = 'FRI'
GROUP BY deptno
UNION ALL
SELECT deptno, 0
FROM scott.emp
GROUP BY deptno
)
SELECT deptno, MAX(fri_count) fri_count
FROM e
GROUP BY deptno
ORDER BY 1```

All the above techniques may look cool but they are clearly an overkill for such a simple problem. There is a simple rule worth remembering:

If you need to conditionally aggregate all records in the table but you fail doing so due to a WHERE clause filter, consider moving the filter into the GROUP function you use in SELECT.

Strategy #5: Conditional Aggregation

```SELECT deptno, COUNT(DECODE(TO_CHAR(hiredate, 'DY'), 'FRI', 1)) fri_count
FROM scott.emp
GROUP BY deptno
ORDER BY 1```

Alternatively, you can use CASE function inside of COUNT. It is especially convenient for our original question/problem, i.e. to count employees who is not paid a commission:

```SELECT deptno, COUNT(CASE WHEN LNNVL(comm>0) THEN 1 END) no_comm_count
FROM scott.emp
GROUP BY deptno
ORDER BY 1```
DEPTNO NO_COMM_COUNT
10 3
20 5
30 3

This approach is the most efficient as it makes Oracle scanning the emp table only once.

Notice the use of the LNNVL function. You can read more about it in my recent post here.

If you like this post, you may want to join my 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”.

## SQL puzzle: Find unique specialists in every department

Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.

Find all employees who has a unique job title in their respective department.

Solution #1: Using NOT EXISTS

```SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job
AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #1.1 – Generic substitution for NOT EXISTS

```SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job
AND a.empno!=b.empno)
ORDER BY deptno, job
```

Solution #2: Using NOT IN

```SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
FROM emp b
WHERE a.deptno=b.deptno
AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.

```SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
FROM emp b
WHERE a.empno!=b.empno)
ORDER BY deptno, job
```

Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)

```SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #3.2: A mixed version of Solutions #1.1 and #3.1:

```SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
FROM emp b
WHERE a.deptno=b.deptno
AND a.job=b.job)
ORDER BY deptno, job
```

Solution #4: Using Analytical function COUNT

```WITH x AS (
SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE cnt=1
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4

```WITH x AS (
SELECT ename, deptno, job, sal,
MAX(empno) OVER(PARTITION BY deptno, job) max_no,
MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job
```

Solution #5: Using In-Line view (WITH)

```WITH x AS (
SELECT deptno, job
FROM emp
GROUP BY deptno, job
HAVING COUNT(*)=1
)
SELECT ename, deptno, job, sal
FROM emp JOIN x USING (deptno, job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850
```

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