silverstarmedia This question is poorly written. Note the possible ways to interpret:
"Write a query to print the sum of LAT_N and LONG_W up to 2 decimal places separated by space."
This could read as "Print the sum of LAT_N + the sum of LONG_W", or "Print the sum of each column". Also, the problem mentions nothing of ROUNDING the decimals. As a result, TRUNCATE doesn't work, which based on the problem should be the (more) correct function.
So for anyone confused about what is being requested, print the sum of each column, ROUNDED to two decimals.
mayurgotechaAsked to answer [deleted]silverstarmedia Thanks for your reply. I had already successfully submitted prior to posting my comment, but thanks anyways. I was just pointing out that the problem doesn't mention anything regarding "rounding" to two decimals, and that the language itself is kind of vague.
prashichary but when i running this select round(sum(lat_n+long_w),2), not working y???
silverstarmedia That's why I was pointing out the problem being worded vaguely. They're looking for the rounded sum of each of the values, separated by a space.
sy321539 select ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) from STATION;
tadeuaugusto exactly!
vikramadityas46 very well
nsirimailbox I have written the same query.. but its not working..
avinash34 [deleted]avinash34 he is asking to print two values. by doing round(sum(lat_n+long_w),2) you get only one value.
hashvikash Not working in MS SQL Server :-/
kdctechie Same here, fortunately my query was not using MS SQL specific functions and I just submitted it under MySQL
prlpzb The round function outputs 42850.04 47381.48 in MySQL but it outputs 42850.0400000000 47381.4800000000 in MS SQL Server. Quite weird.
leonidas2381 Yes, that happens in MS SQL. One way to work around it is to cast answers to decimal type with 2 decimals:
SELECT CAST(round(sum(lat_n),2) as decimal (10,2)), CAST(round(sum(long_w),2) as decimal (10,2)) FROM station;
Then MS SQL outputs: 42850.04 47381.48
Apeksha_Mehta This worked for me in MS SQL
SELECT CAST(SUM(LAT_N) AS NUMERIC(10,2)) as lat, CAST(SUM(LONG_W) AS NUMERIC(10,2)) as lon FROM STATION
oscarRussi thanks, it was helpful
yakovm1991 [deleted]
AshWillis How many decimal places are you outputting? Rounding changes the data but not the data type. The data type in the database has higher precision so you need to cast to a type of less precision. I used a decimal(10,2)
skibum93 For MS SQL Server I placed the round in a left function. Not a clean answer, but it got the job done
SELECT LEFT(ROUND(SUM(Lat_N ),2),8), LEFT(ROUND(SUM(Long_W), 2),8) FROM Station;
azarang Its a bad answer ! you knew the output length, so LEFT operation is the worst answer in this problem :(
senen Try with: FORMAT(SUM(LAT_N), 'F2') ...
sjalan1 thanks:) worked in ms sql.please can u explain the meaning of F2
nicholas_a_staf1 F means floating point type 2 means number of decimal places
similar syntax to printf function in C
ramey_steven Great answer. Thanks.
ladywho SELECT CONVERT(decimal(10,2),SUM(LAT_N)), CONVERT(decimal(10,2),SUM(LONG_W)) from station;
denis_ariel Yeah, for MS SQL you need to cast as numeric(10,2) after rounding so it will show only two decimal places.
select cast(round(sum(lat_n), 2) as muneric(10,2))
piyusman [deleted]
AvmnuSngHackerRank Admin How can you write sum(LAT_N + LONG_W) SEPARATED BY SPACE?
silverstarmedia You need to find the rounded sum of each column. The output format should loom like "val1 val2".
AvmnuSngHackerRank Admin This is why, separated by space clause is there in the statement.
andrewstuart There is a very very big difference between returning the answer in separate columns versus returning the results in a single concatenated string, and the answer does not distinguish between those two. You'll find almost no real-world scenarios where you actually care if they're separated by a space. What you want is separate columns.
dibyajyoti_bein1 [deleted]
gabrieldarezzo Thank men!!! I really get nerveus until read this explanation kkk.
piyusman Select round(Sum(LAT_N),2),round(Sum(LONG_W),2) from STATION
souvikmodak select Round(sum(LAT_N),2),Round(sum(LONG_W),2) from station;
rajashekarr440 why did you wrote number 2 there
austin_wade55 Number of decimal spaces
rohitsawai2893 Thank you friend
onkar_rana27 select cast(Sum(LAT_N) as decimal(10,2)) lat ,cast(Sum(LONG_W) as decimal(10,2)) lon from STATION;
sonergonul It is kind of weird the engine does not accept this in MSSQL;
SELECT ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) FROM STATION;
I would like to hear why this won't work.
ggeorgiou_gr Works fine.
select round(sum(LAT_N),2) as lat, round(sum(LONG_W),2) as lon from Station
nibedita91 [deleted]
chenjesu It's not truncated to 2 decimal points. It ends up getting something like 196.03000000000, rather than 196.03.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT ROUND(SUM(Lat_N), 2), ROUND(SUM(Long_W), 2) FROM Station;
Feel free to ask if you have any questions :)
edlaierii Issue with MS SQL Server is that Round doesn't give decimal precision, my answer shows how to format your answer with a number in two differing ways.
Select Format(Round(Sum(LAT_N),2),'F2'), Format(Round(Sum(long_w),2),'#.00') from station
sandrading0923 Your answer is correct! Thx!
bhardwajanjali2 select cast(sum(LAT_N) as decimal(10,2)) as lat, cast(sum(LONG_W) as decimal(10,2)) as lon from Station; This will work fine for MS SQL Server.
hbautista sql server:
select convert(decimal(10,2),sum(LAT_N)), convert(decimal(10,2),sum(LONG_W)) from station
dukemaneesh SELECT ROUND(sum(LAT_N,'2')),ROUND(sum(LONG_W,'2')) FROM STATION what is wrong with this code?
DeepamSehgal no need of quotes around 2...
Gnany99 "SELECT ROUND(SUM(lAT_N),2),' ',ROUND(SUM(LONG_W),2) FROM STATION;" WORKING IN SQL BUT NOT IN MS SQL SERVER.. PLEASE HELP
Aditya96Asked to answer use cast instead of round in ms sql server.
BePerfect Same here.........
sabiqueakhan Try this! SELECT CAST(CAST(SUM(LAT_N) AS DECIMAL(20,2)) AS VARCHAR(50))+' '+CAST(CAST(SUM(LONG_W) AS DECIMAL(20,2)) AS VARCHAR(50)) FROM station;
ebartan its work mssql
prateek_11dec so helpful..
mohd_rafi0195 '' it is why used
shivu_narang my queries are not working in ms sql server select round(sum(lat_n),2) from station;
select round(sum(long_w),2) from station; what is wrong with this
deediwuAsked to answer You need to print these 2 number separated by space instead of breaking them into 2 queries.
abhaykumar09 The question is really confusing, I tried the both possile version of the query -> first, query to display sum of Lat_n and Long_w for each row rounded upto 2 decimal.
-> second, only one output as (sum of all lat_n rounded to 2 decimal)+an space+(sum of all long_w rounded to 2 decimal).
ut both output is incorrect. Don't know why?
DarendalAsked to answer How are you doing the rounding? If you're using the Round() function, you're rounding down when the decimal value is less that O.5, but rounding up if the decimal value is >= 0.5.
Try converting to a decimal instead, which will simply chop off any decimals after the second decimal place
Guramidev [deleted]
GuramidevAsked to answer submit with mysql, there are some bugs here with oracle
AvmnuSngHackerRank Admin what kind of issue with Oracle, The problems is tested for all the options allowed to submit the query.
Guramidev SELECT ROUND( SUM( LAT_N ),2 ),ROUND( SUM( LONG_W ),2 ) FROM STATION works with MySQL but doesn't work with Oracle. As far as i know, it should work perfectly with Oracle too but here it just returnes an empty response.
AvmnuSngHackerRank Admin I think you should put a
';'and it will work for sure. Never forgot to follow the rules.And also it will work fine even without ';'.Guramidev I think you don't hear me. I don't have any reason to lie, and as a moderator it would be more logical to be interested in issue rather than keep telling me that there is no any bug. All my successfull submissions with Oracle end without ';' statement. Only this one doesn't work without ';' , see screen ( http://i.imgur.com/SA0cl3i.png?1 ). Can you tell me, when out of 20 things 19 behave same and 1 doesn't, what we call that one thing?
AvmnuSngHackerRank Admin There are many submissions in Oracle. There may be a bug, we will check whats the issue, but It's wroking fine. Check http://i.imgur.com/Rtb9nw2.png?1, the same query works perfectly fine, and submission is from your account.
VishiGulati SELECT ROUND( SUM( LAT_N ),2 ),ROUND( SUM( LONG_W ),2 ) FROM STATION; IT WORKS IN ORACLE
VishiGulati SELECT ROUND( SUM( LAT_N ),2 ),ROUND( SUM( LONG_W ),2 ) FROM STATION; IT WORKS IN ORACLE
baranque The following should work in MySQL and Oracle:
select ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) from STATION;
However, I get the following error in both: Compiler Message Wrong Answer
Using the solutions in the Discussions is not working for me. How can I trouble shoot this?
Sort 127 Discussions, By:
Please Login in order to post a comment