Sort 81 Discussions, By:
Please Login in order to post a comment
Use of Round is improper here since it rounds Up in cases where decimal values are >= 0.5. Floor should be used instead since we're asked to round Down. Note that with MS SQL Server, when averaging an Integer such as the Population field, the average function does not cause implicit conversion to Float so decimal values are ignored and applying Floor is unnecessary.
select Round(avg(population)) from city;
ROUND(); rounds UP
FLOOR() : rounds DOWN
Those who are using round use -0.5 .
Code for mysql using round
WHY ARENT U WRITING GROUP BY DISTRICT AT THE LAST
i have tried above query it is not working.
I just tested and it's working. Please try again and let me to know the result. NOTE: Please use Oracle DB
I think the staff here should update the problem as ROUND() Is passing the test case but the most consistent answer would use the FLOOR() function.
Here is Oracle solution from my HackerrankPractice repository:
Feel free to ask if you have any questions :)
SQL Server Rounding Functions - Round, Ceiling and Floor
SELECT FLOOR(AVG(POPULATION)) FROM CITY
I GUESS HERE IT WOULD BE FLOOR MORE APPROPRIATE RATHER THAN ROUND IF NOT PLEASE EXPLAIN
WHY IS THIS WRONG..? SELECT FLOOR(AVG(POPULATION)) FROM CITY GROUP BY DISTRICT;
Consider the following to Explain your Question =>
SELECT product, SUM(sale) AS "Total sales"
GROUP BY product;
Now, Coming back to your question
=> SELECT FLOOR(AVG(POPULATION)) FROM CITY GROUP BY DISTRICT;
All select statements with Aggregate function DO NOT NECESSARILY need GROUP BY clause. Unlike the Eg. above, You are not selecting and other field except the aggregate field i.e. 'Popoulation'.
Group By on 'District' would generate mutiple groups of 'Disrict' as a result set. i.e. =>
But what these guys are looking for is rather just a single values. Hence,
Both work and are accepted. Hope this answers your question.
Also note that we can use oracle round function for the same to achieve the same result,
select round(avg(population)) from city;
WHY IS THIS WRONG..?
GROUP BY DISTRICT;
Why are you grouping it by district. Possibly, the program is trying to calculate multiple avg. values and throws a error.
Let me know if you have any questions!
(From my HackerRank repo on GitHub.)
select truncate(avg(population),0) from city
This has worked in MSSQL:
Select Convert(Int,Floor(Avg(Convert(Float,Population)))) from