40. The Blunder: Solution

---

Samantha was tasked with calculating the average monthly salaries for all employees in the EMPLOYEES table, but did not realize her keyboard’s 0 key was broken until after completing the calculation. She wants your help finding the difference between her miscalculation (using salaries with any zeros removed), and the actual average salary.

Write a query calculating the amount of error (i.e.: actual - miscalculated average monthly salaries), and round it up to the next integer.

The EMPLOYEES table is described as follows:

Note: Salary is per month.

Constraints

$1000 < \text{Salary} < 10^5$.

Sample Input

Sample Output

2061

Explanation

The table below shows the salaries without zeros as they were entered by Samantha:

Samantha computes an average salary of 98.00. The actual average salary is 2159.00.

The resulting error between the two calculations is 2159.00 - 98.00 = 2061.00. Since it is equal to the integer 2061, it does not get rounded up.

We will use the following Oracle string functions to construct our query:

1. TO_CHAR(NUM), which converts integer NUM to string.
2. REPLACE(STR, X, Y), which replaces character, X, in string, STR, with character, Y.
3. TO_NUMBER(STR), which converts string STR to the corresponding integer.

In order to compute the miscalulated average, we will first convert each SALARY value to string using TO_CHAR(SALARY). Then we remove all 0’s by using REPLACE(TO_CHAR(SALARY), '0', ''). Finally, we convert the resulting string back to an integer using TO_NUMBER(REPLACE(TO_CHAR(SALARY), '0', ''). This gives us the SALARY values after removing all zeros. Hence our final query is as follows:

SELECT CEIL(AVG(SALARY) - AVG(TO_NUMBER(REPLACE(TO_CHAR(SALARY), '0', ''))))
FROM EMPLOYEES;

---

Back to problems

---

anusha-murali.github.io