Some error occured while loading page for you. Please try again.
Sort 28 Discussions, By:
Please Login in order to post a comment
Here is Oracle solution from my HackerrankPractice repository:
WHERE District = 'California';
Feel free to ask if you have any questions :)
To get it as float
select round(avg(cast(population as float)),3)
where district = 'California'
This solution works for any of the listed RDBMS:
DB2, mySQL, Oracle, SQL Server.
WHERE district = 'California';
Anyhow there are discrepancies in the values displayed for DB2, mySQL and SQL Server.
When compared to the expected result shown, while the solution run under Oracle shows the exact decimal precision, mySQL shows more digits after the decimal point and DB2 and SQL Server show only the integer part of the average value. Anyhow the interpreter considers the solution correct for any of the RDBMSs.
Trying to meet the exact value expected when running the script under DB2, I tested a CAST() solution proposed at this website: https://www.w3resource.com/sql/aggregate-functions/avg-decimal-places-using-cast-within-avg.php, but got a runtime error:
/* DB2 solution to meet exact decimal precision */
SELECT CAST(AVG(population) AS DECIMAL(6, 3))
WHERE district = 'California';
Your code did not pass this test case.
Your Output (stdout)
SQL0413N Overflow occurred during numeric data type conversion.
Can you guys tell me anyworkaround to get the results I want and if there is anything or any specific limit in the challenge environment?
All the best!
Problem: AVG rounded to an integer value (the same data type as the values), so the decimal values are lost before casting to decimal.
Solution: CAST 'population' data type first, use AVG, then ROUND.
SELECT ROUND(AVG(CAST(POPULATION AS FLOAT)),3)
Try in MS SQL
select avg(Population) from CITY where District='California'
--I typed in the exact same code that you have and it wouldn't run, then I pasted your code (which is exactly the same) and it ran. I noticed the only difference was that your code seemed to pick up on 'select ave' as an actual function (both changed to the color purple), whereas when I typed mine in it only recognized the 'select' as an actual function and 'ave' was left as a grey color. Any idea why it doesn't automatically pick up on 'ave' as an actual function?
Hey!! that's not 'ave' that is 'AVG' try 'avg' there and then run it.
You're right and I can't believe I didn't notice that! It works perfectly now. Thanks for the fast reply!
You're most welcome..
select (sum(population) / count(name)) from city where district = 'California';
little long but works
Select avg(Population) From City where district ="california"
ERROR at line 1:
ORA-00904: "california": invalid identifier
what should i do?
Use single quotes instead of double quotes when quoting strings.
select avg(population) from city where district='California';
Where clause must start with capital letter.
select avg(population) from city where District='California';
select avg(population) from city where district = 'california';