camcrews 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.
souvikmodak select Round(avg(population)) from city;
bourax ROUND(); rounds UP FLOOR() : rounds DOWN
rahul_suman Those who are using round use -0.5 .
Code for mysql using round
SELECT ROUND(AVG(POPULATION)-0.5) FROM CITY;
shauryaprataps21 WHY ARENT U WRITING GROUP BY DISTRICT AT THE LAST
tsvetoslav_tsve1 Oracle:
SELECT FLOOR(AVG(population)) FROM city;
UCCADI i have tried above query it is not working.
tsvetoslav_tsve1 I just tested and it's working. Please try again and let me to know the result. NOTE: Please use Oracle DB
yvanjaquino 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.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT FLOOR(AVG(Population)) FROM City;
Feel free to ask if you have any questions :)
sanjay_dev 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
shauryaprataps21 WHY IS THIS WRONG..? SELECT FLOOR(AVG(POPULATION)) FROM CITY GROUP BY DISTRICT;
neerajshukla989 Consider the following to Explain your Question =>
SELECT product, SUM(sale) AS "Total sales" FROM order_details GROUP BY product;
Because we have listed one column (the product field) in your SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY clause. The product field must, therefore, be listed in the GROUP BY clause.
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. =>
300000 201360 197408 185957 142889 530000 120000 116178 160516 137400 145900 120758 42326
94526 1191043 940968 128857 159103 186076 245772 4694000 563662 252386 122753 192389 273140 ......
But what these guys are looking for is rather just a single values. Hence,
SELECT ROUND(AVG(POPULATION)) FROM CITY; --454,250
SELECT FLOOR(AVG(POPULATION)) FROM CITY; --454,250
Both work and are accepted. Hope this answers your question.
mkashivaji Also note that we can use oracle round function for the same to achieve the same result, select round(avg(population)) from city;
dvt32 MySQL:
SELECT FLOOR(AVG(POPULATION)) FROM CITY;
shauryaprataps21 WHY IS THIS WRONG..? SELECT FLOOR(AVG(POPULATION)) FROM CITY GROUP BY DISTRICT;
adityaanand2174 Why are you grouping it by district. Possibly, the program is trying to calculate multiple avg. values and throws a error.
_clfm_ MySQL - clean & correct
Let me know if you have any questions!
SELECT FLOOR(AVG(POPULATION)) FROM CITY;
(From my HackerRank repo on GitHub.)
MJ0622 mysql: select truncate(avg(population),0) from city
rasujasin This has worked in MSSQL:
Select Convert(Int,Floor(Avg(Convert(Float,Population)))) from City
Sort 81 Discussions, By:
Please Login in order to post a comment