WHY DO YOU WRITE [DELETED] ...WHAT DOES THAT MEAN

I honestly think it might be bugged.

So do with me

don`t left blank between the ''

Please ping your solution. So I can help you in correcting it

i think you using round instead of ceil.

I did mine similarly; here's how I'd explain it:

SELECT CEIL( # CEIL rounds the argument up to the nearest int
	AVG(Salary) # the correct average of salaries
    - AVG( # subtract the incorrect average
    	REPLACE(Salary,'0','') # replace all occurances of '0' in Salary with ''
        )) FROM EMPLOYEES;

My Solution: SELECT CEIL(AVG(Salary)-AVG(REPLACE(Salary,'0',''))) FROM EMPLOYEES;

Splitting into parts:
CEIL: They asked to print the immediate next value. So used CEIL to get the next value....... REPLACE(Salary,'0',''): This will replace all the zeros with blank. Ex: 90780 will turn into 978...... AVG(Salary): Gives the Average total salary of all the employees...... AVG(REPLACE(Salary,'0','')): Gives the Average total salary of all the employees after removing zeros from the salaries...... (AVG(Salary)-AVG(REPLACE(Salary,'0',''))): This is the final difference which is asked in the question

Did yoy try the solution and faced any issue? I just checked the solution again and worked fine in my system. Note: this code is for "MySql" users. If you face any issue, please comment your code, so I can help you in editing your solution.

can u xplain mate?

Thanks mathewpylichen ! At first I casted that salary(s) to int but realized casting them to decimal then ceiling is more accurate.

Please try it out in MySql

Found the solution after many trails.Good work.Thank you.

you can use it with a cast (float to int). here is my code.

sql

select cast(ceiling(avg(cast(salary as float))-avg(convert(float, replace(str(salary), '0', '')))) as int) from employees;

Yes it worked.Thanx..!

Why are you casting it to Decimal? If we don't then you get "Operand data type varchar is invalid for avg operator" this error. Can someone explain please.

Msg 8117, Level 16, State 1, Line 2 Operand data type varchar is invalid for avg operator.

while taking number into REPLACE function, it will auto cast into String and does the operation. And after finsihing the function, it will auto cast to the original datatype

Return the ceiling of x as an Integral. This is the smallest integer >= x.

I guess its not a bug. The Salary type is integer in the table. So an avg between ints will be int.

Yeah you are right, it was the avg function that return a int value, thanks.

Y we are casting the value? if it is already in int. SELECT CEIL(AVG(SALARY)- AVG(REPLACE(SALARY,'0',''))) FROM Employees; is this a correct solution?

Still facing the same issue. Can anyone help to fix this

I tried this for MS SQL:

declare @A as int; declare @B as int;

SELECT @A = CEILING(AVG(Salary)) FROM Employees WHERE SALARY < 100000; SELECT @B = CEILING(AVG(cast(replace(cast(Salary as varchar(max)), '0', '') as numeric(16,4)))) FROM Employees WHERE SALARY < 100000;

SELECT @A-@B;

It returns 2251. I tried other stuff and 2250 and 2251 were not accepted.

Thanks, that was the error!

But...they asked us to make the difference between..actual and computed..is this the way to make the computed thing as null..and then display the normal average rounded of to next integer?? But i was thinking of how we can compute the miscalculated valu!! :)..thanks for the answer..

Indeed, I got: 2252.25 and it's the actual answer: SELECT ((AVG(ACTUAL_SALARY)) - (AVG(WRONG_SALARY))) FROM ( SELECT TO_NUMBER(REPLACE(TO_CHAR(SALARY),'0', '')) AS WRONG_SALARY, SALARY AS ACTUAL_SALARY FROM EMPLOYEES );

Fair enough. In the real world, currency would never be stored as int because it requires this type of precision. Perhaps the question could be tweaked to avoid this confusion. i.e. this could be the number of something rather than salary.