We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Aggregation
- The Blunder
- Discussions
The Blunder
The Blunder
Sort by
recency
|
1708 Discussions
|
Please Login in order to post a comment
SELECT CEIL( AVG(CAST(em.salary AS DECIMAL)) - AVG(CAST(REPLACE(CAST(em.salary AS CHAR), '0', '') AS DECIMAL)) ) AS difference FROM employees em;
select ceiling(avg(salary)-avg(replace(salary,0,''))) from employees;
MS SQL SERVER-
SELECT CEILING(AVG(CAST(SALARY AS DECIMAL))-AVG(CAST(REPLACE(SALARY,0,'')AS DECIMAL))) FROM EMPLOYEES;
For those, who are getting the wrong result as 2252 and not actual 2253, this is because of the first query- AVG(SALARY)
In SQL Server, AVG function returns the result as integer even if the real result is decimal and that's why query is failing.
So, you have to encapsulate this query under cast too- AVG(CAST(SALARY AS DECIMAL)) Now, write together the whole query and you'll get the answer.
SELECT CEILING(AVG(salary) - AVG(REPLACE(salary,0,""))) FROM employees
Actual query was select ceil(avg(Salary)-avg(Replace(Salary,0,''))) from Employees; but what I have to do was select ceil(avg(Salary)-avg(Replace(Salary,0,''))+1) from Employees; Any suggestion