Prepare > SQL > Aggregation > The Blunder

 

The Blunder | HackerRank

 

문제

---

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(actual - miscalculated), and round it up to the next integer.

 

=> 사만다가 사용자들의 월급 평균을 구하려는데 키보드의 0 이 망가져 잘 못 계산 했다. 실제와 오류의 차이를 구해 올림하여라.

 

 

 

 

코드

---

SELECT CEIL(AVG(Salary) - AVG(replace(Salary, 0, ''))) FROM EMPLOYEES;

 

 

두 번째 시도

SELECT 
CEIL(AVG(SALARY - replAce(salary, 0 , '')))
FROM EMPLOYEES;

 

 

 

 

참조

---

[MSSQL] 문자열 치환 방법 3가지 (REPLACE 함수)