vivek_23 if anyone is using round function instead of floor , then just do
round(avg(City.Population)-0.5)to round itdownto nearest Integer.
elv0816 Thank you!
vivek_23 welcome
divyad1130 why is that so? Can you please explain? I wrote the code similar without what you mentioned. It didnt work. But, when I used your suggestion, it works!
select country.continent, round(avg(city.population),0) from country inner join city on country.code=city.countrycode group by country.continent;
vivek_23 because in the problem statement it says - " rounded
downto the nearest integer ". So, if the avg value is 9999.5 or greater in decimal points, it should return 9999 and not 10000 (which only round() would have done).Best choice is to use floor(), else do the workaround that I did.
manshitomarleo i used floor before. but it didn't work. but when i used the round as suggested above, it worked! why?
vivek_23 Can I have a look at your query?
tashmatbaltabai SELECT co.continent, TRUNC(AVG(c.population)) FROM city c JOIN COUNTRY co ON c.countrycode = co.code GROUP BY co.continent;
darsuratwala floor worked for me
sevo_jlee it seems like all values are integer as i passed task without round function lol
dpanwala1 good logic ! But floor would be fine, ain't it ?
vivek_23 Yeah , I just provided an alternative method !
EvgenyOrlov It's not a right method by the way. For negative numbers ;)
vivek_23 Correct, but its serves just fine for the scope of this problem as average of a population can never go negative.
givonz yes. but still not working. not sure why. i assume i am not excluding continents w/o cities.
vivek_23 May be . Can you paste your query ? (or better paste a submission link to it )
gee_kiss_sexy I'm on the same page! Here's the query
select country.continent, city.name, round(avg(city.population)) from country join city on city.countrycode = country.code GROUP BY country.continent, city.name;
I also used
select distinct country.continent, city.name, round(avg(city.population)) from country join city on city.countrycode = country.code GROUP BY country.continent, city.name;
and even
select distinct country.continent, city.name, round(avg(city.population)) from country join city on city.countrycode = country.code where city.name is not null GROUP BY country.continent, city.name;
What do you suggest?
vivek_23 We only need 2 columns in the result set,i.e.,
continentandaverage population. Remove city.name from your query.You have used round which is correct, but this problem demands us to round the value down to the nearest integer. So, you will have to use either
floor()or subtract 0.5 from the avg value and apply round on it(this would go the base integer instead of moving to next one if the decimal part of avg happend to be >=0.5). I used below queries-select c.continent,floor(avg(ci.population)) as 'avg_population' from city ci inner join country c ON ci.countrycode = c.code group by c.continent;
OR
select c.continent,round(avg(ci.population)-0.5) as 'avg_population' from city ci inner join country c ON ci.countrycode = c.code group by c.continent;
suhas_rm2009 WHEN TO USE GROUP BY CLAUSE?
alireza_saberi wrong
vivek_23 What is wrong?
siddhantsaxena shouldn't there be a having clause with group by to account for including only cities that have population greater than 0
vivek_23 This is not mentioned in the problem statement anywhere.
priyankanaik042 Thanks, this helped :).... can you just tell, if instead round up was asked, how could we do it?
vivek_23 For round up - you can just do
+ 0.5instead of- 0.5.Like => round(avg(City.Population)+0.5)
vivek_23 Since average population will not be a negative value, +0.5 or -0.5 will work correctly.
But we need to be careful when it comes to rounding of negative floating points.(better to use round() function in this case)
ameykulkarni Thank you for pointing that out !
vivek_23 Welcome.
Hanzhuo Thanks, very helpful!
irshadnilam thank you ..
kaushik_iyer Why does not this round(avg(City.Population),0) work instead ofround(avg(City.Population)-0.5) ?
round(avg(City.Population),0) also rounds it to the nearest integer
vivek_23 We need to round it
downto the nearest integer.So, if the value is384.5or384.7, we need to output384and not385.If you just use round for this , it will round to thenextinteger.pdhomne @kaushik_iyer I was having the same doubt. Thanks vivek_23 for clearing it.
kt_k7 Thanks was getting wrong for no reason ..
darsh_mehta Use floor() instead of round()
fjhahn so floor is sort of link truncate then.. good to know
abhinaya46 thnks it helped...
mkashivaji I don't see any need to do extra calculation rather use truncate function, it takes care about rounding off to the nearest integer value.
Code as below,
select co.continent, trunc(avg(ci.population)) from city ci,country co where co.code = ci.countrycode group by co.continent;
Madhuram_Kabra Thanks bro, solved my error
ashwinap [deleted]
rajatdhoot Thanks for Explaination.......
nyomanyudis1995 Thanks
umolakal Thanks, Could n't figure out what's wrong. spent so much time. Great help!
pky329 THX vivek
sen_gao_dev perhaps we use floor or ceil when dealing with rounding? I use floor, it passed.
vivek_23 Yes, floor is the best way to go about it.
kmadarsh123 Y we have to write -0.5??can u plz explain
vivek_23 Since I used
round(), I need toround it downto the nearest integer.
ashishmangal_in1 why we have to subtract -0.5 can u please tell me?
vivek_23 Since I used
round(), I need toround it downto the nearest integer.
sumitlohani20 but the question is saying nearest integer not lower integer and ur comment is rounding it to lower integer not nearest suppose avg is 243.9 and according to u it becomes 243.9-0.5=243.4 and then rounding it gives the upper integer always which is floor value /////though it should five 244 as answer for nearest integer.
Use FLOOR(), if you want to round your decimal to the lower integer. Examples:
FLOOR(1.9) => 1 FLOOR(1.1) => 1 Use ROUND(), if you want to round your decimal to the nearest integer. Examples:
ROUND(1.9) => 2 ROUND(1.1) => 1 Use CEIL(), if you want to round your decimal to the upper integer. Examples:
CEIL(1.9) => 2 CEIL(1.1) => 2
vivek_23 The problem statement says round it down
to the nearest integer.. Hence, it's the lower integer. You can also use floor(which is the best way to go about it).
RodneyShag MySQL solution
From my HackerRank solutions.
SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY INNER JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT;
Let me know if you have any questions.
forPublic Sorry, but why group by ?
kiranpadre Since we have used average city population grouped by country contenent
sivaandsivabe To use aggregate function like average along with country
codegritty How come GROUP BY 1 doesn't work? It's the same column as your GROUP BY COUNTRY.CONTINENT
salmanhaque07 it is logically correct .please select mysql then try it.
priyankasomyaco1 not working..compiler error
RodneyShag I just tested this code and it compiles. Make sure you use MySQL.
Bhavesh_Infosys Do not include continents without cities in your output; where u checked this constraint?
THANK U :)
Bhavesh_Infosys THQ :)
madhan_haran FLOOR MEANS ?
rupeshsaw786 Lets x=4.25 WHEN USED FLOOR(x) this will return the 4 lets take one onother example x=4.95 Floor(x) This will also return the 4 floor function return u the floor value
madhan_haran FLOOR MEANS?
RodneyShag It rounds down. I googled "mySQL floor function" and got this as 1st link: http://www.w3resource.com/mysql/mathematical-functions/mysql-floor-function.php
diegomsaiz Thanks a lot, pal!! I have realized that is mandatory to use GROUP BY at the end of the select sentence in order to may use of COUNTRY.CONTINENT at its beggining.
kera404 why does
SELECT COUNTRY.CONTINENT, round(AVG(city.population)) from city join country on country.code=city.countrycode group by country.continent
is giving a wrong solution. Although the output from your code as well as the above code are the same
RodneyShag Change round to floor and it should work. Although the output looks the same, the actual numbers are being rounded differently show our answers are not exactly the same.
Sowmeya why you used inner join???
RodneyShag I wanted to join the 2 lists and grab the intersectin. More info about joins is available here.
[deleted] SELECT floor(avg(city.population)), country.continent from city INNER JOIN country ON city.countrycode=country.code GROUP BY country.continent;
CAN YOU PLEASE TELL WHAT IS WRONG IN THIS? IT IS NOT WORKING IN MYSQL.
yashbadia19 SELECT CN.CONTINENT,FLOOR(AVG(C.POPULATION)) FROM COUNTRY CN, CITY C WHERE C.COUNTRYCODE=CN.CODE GROUP BY CN.CONTINENT ;
2digantkaushik [deleted]
publysher This question needs some love. First of all, make clear you're looking for the average city population per continent. Furthermore, also make clear that countries without cities (Antarctica) should not be included.
abhiranjan Thanks for pointing. Now it's fixed.
Sorry for the delay.
bablobko Yes when there is lack of Clarity. You definitely need LOVE to hear what has been unsaid and say what has never been heard.
srinidelite but what's wrong with my answer it still says wrong answer .. this system sucks !
select country.Continent ,floor ( avg (city.population)) from city , country where City.CountryCode = Country.Code group by country.Continent ;
iqbalwasim use inner join
dcc1079 @iqbalwasim forgive me if I'm misunderstanding something, but I believe @srinidelite's code DOES involve an inner join; saying "table1.field1 = table2.field2" is just an alternative syntax.
For an exemplary discussion of the different types of joins, see Mark Harrison's answer and Thomas' comment at http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins
Levia Just for future reference, the concept of inner/outer/natural join is separate from the condition.
sadiyaj_07 select COUNTRY.CONTINENT, floor(avg(CITY.POPULATION)+0.5) FROM CITY , COUNTRY WHERE CITY.COUNTRYCODE=COUNTRY.CODE GROUP BY COUNTRY.CONTINENT
ParasDoshi +1! It's still not that clear.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT;
Feel free to ask if you have any questions :)
aarshsheth3 [deleted]
srvb1234 why should we use group by function here ??
chadwalt SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE WHERE CITY.NAME IS NOT NULL GROUP BY COUNTRY.CONTINENT;
sonergonul For MS SQL Server, mine is;
SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION)) FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE GROUP BY COUNTRY.CONTINENT;
joshiabhi205 Can anyone please help me why I am am getting error with the below sql query for Oracle : select COUNTRY.CONTINENT,ROUND(AVG(COUNTRY.POPULATION),0) from CITY inner join on COUNTRY where CITY.COUNTRYCODE = COUNTRY.CODE;
amarjeetanand the question says rounded down to the nearest integer.
So i think round() function should be used. Why using floor() clears TC??
SELECT country.continent, FLOOR(AVG(city.population)) FROM country INNER JOIN city ON city.countrycode = country.code GROUP BY country.continent;
pushkar_shv floor function will turn a decimal value into smallest and nearest non-decimal value.eg:- floor(45.9)=45; floor(34.1)=34 and Ceiling funtion will turn a decimal value into largest an d nearest non-decimal value.eg:-ceiling(45.1)=46;celing(33.9)=40. round functions are used for more specific rounding of certain values.
nam097 [deleted]
WittyNotes Here's a Mysql solution that passes, although I'm still a little hazy on how the 'group by' function works here.
select country.continent, truncate(avg(city.population), 0) from city inner join country on city.countrycode = country.code group by country.continent
Sort 251 Discussions, By:
Please Login in order to post a comment