[MYSQL] The Blunder

▶  SQL  >  Aggregation  > The Blunder 

 Problem 

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 - misclaculated average monthly salaries), and round it up to the next integer.

→ 0을 제거한 월급의 평균과 실제 월급의 평균의 차이를 올림한 값을 쿼리하라

 

Input Format

The EMPLOYEES table is described as follows:

Note: Salary is per month.

Constraints

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

 

 My Answer 

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

 

 NOTE 

• CEIL(숫자) : 올림
• ROUND(숫자, (자릿수)) : 반올림
• TRUNCATE(숫자, 자릿수) : 내림
• FLOOR(숫자) : 소숫점 아래 버림
• ABS(숫자) : 절대값
• POW(X, Y), POWER(X, Y) : X의 Y승
• MOD(X, Y) : X를 Y로 나눈 나머지

[ 문자열 변경 함수 ]

REPLACE('문자열', '기존의 변경될 문자열', '변경할 문자열')