We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
Loading...
  1. Practice
  2. SQL
  3. Aggregation
  4. Weather Observation Station 2
  5. Discussions

Weather Observation Station 2

Sort 148 Discussions, By:

votes

  • silverstarmedia + 7 comments

    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 + 4 comments
      [deleted]
      • silverstarmedia + 0 comments

        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 + 4 comments

        but when i running this select round(sum(lat_n+long_w),2), not working y???

        • silverstarmedia + 0 comments

          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 + 4 comments

          select ROUND(SUM(LAT_N), 2), ROUND(SUM(LONG_W), 2) from STATION;

          • tadeuaugusto + 0 comments

            exactly!

          • vikramadityas46 + 0 comments

            very well

          • nsirimailbox + 3 comments

            I have written the same query.. but its not working..

            • hung561992 + 0 comments

              SQL Server select format(sum(lat_n) , '.##'), format(sum(long_w) , '.##') from station;

            • zakir_cse_aust + 1 comment

              select CAST(ROUND(SUM(LAT_N), 2)AS NUMERIC(12,2)), CAST(ROUND(SUM(LONG_W), 2) AS NUMERIC(12,2)) from STATION;

            • steffiesjy + 0 comments

              I think you have to use CAST as a decimal.

          • steffiesjy + 0 comments

            CAST instead of ROUND.

            LAT_N and LONG_W are numbers. When you use ROUND it will keep throwing on 0's to the answer.

        • avinash34 + 0 comments
          [deleted]
        • avinash34 + 0 comments

          he is asking to print two values. by doing round(sum(lat_n+long_w),2) you get only one value.

      • hashvikash + 5 comments

        Not working in MS SQL Server :-/

        • kdctechie + 1 comment

          Same here, fortunately my query was not using MS SQL specific functions and I just submitted it under MySQL

          • prlpzb + 2 comments

            The round function outputs 42850.04 47381.48 in MySQL but it outputs 42850.0400000000 47381.4800000000 in MS SQL Server. Quite weird.

            • leonidas2381 + 4 comments

              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 + 0 comments

                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 + 0 comments

                thanks, it was helpful

              • whiteandrewj4 + 0 comments

                Apparently MS SQL rounds automatically in some cases:

                "By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale" (link to docs)

                Therefore for this query ROUND() isn't actually required.

                SELECT
 CAST(SUM(LAT_N) AS DECIMAL(7,2)),
 CAST(SUM(LONG_W) AS DECIMAL(7,2))
FROM STATION
              • pawitra + 1 comment

                why its 10,2?

                • steffiesjy + 1 comment

                  The 10 is limiting the length of the string, and the 2 specifies the decimal places to round to.

                  I think ive ran this query successfully with just the ,2. You may not have to specifiy the length. Can anyone else comment on that?

                  • pawitra + 0 comments

                    hey thankyou for the explanation!

            • yakovm1991 + 0 comments
              [deleted]
        • AshWillis + 1 comment

          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 + 1 comment

            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 + 0 comments

              Its a bad answer ! you knew the output length, so LEFT operation is the worst answer in this problem :(

        • senen + 2 comments

          Try with: FORMAT(SUM(LAT_N), 'F2') ...

          • sjalan1 + 1 comment

            thanks:) worked in ms sql.please can u explain the meaning of F2

            • nicholas_a_staf1 + 0 comments

              F means floating point type 2 means number of decimal places

              similar syntax to printf function in C

          • ramey_steven + 0 comments

            Great answer. Thanks.

        • ladywho + 0 comments

          SELECT CONVERT(decimal(10,2),SUM(LAT_N)), CONVERT(decimal(10,2),SUM(LONG_W)) from station;

        • denis_ariel + 0 comments

          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 + 0 comments
        [deleted]
    • AvmnuSngHackerRank AdminChallenge Author + 3 comments

      How can you write sum(LAT_N + LONG_W) SEPARATED BY SPACE?

      • silverstarmedia + 1 comment

        You need to find the rounded sum of each column. The output format should loom like "val1 val2".

        • AvmnuSngHackerRank AdminChallenge Author + 0 comments

          This is why, separated by space clause is there in the statement.

      • andrewstuart + 0 comments

        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 + 0 comments
        [deleted]
    • gabrieldarezzo + 0 comments

      Thank men!!! I really get nerveus until read this explanation kkk.

    • piyusman + 0 comments

      Select round(Sum(LAT_N),2),round(Sum(LONG_W),2) from STATION

    • souvikmodak + 1 comment

      select Round(sum(LAT_N),2),Round(sum(LONG_W),2) from station;

      • rajashekarr440 + 1 comment

        why did you wrote number 2 there

        • austin_wade55 + 0 comments

          Number of decimal spaces

    • rohitsawai2893 + 0 comments

      Thank you friend

    • onkar_rana27 + 0 comments

      select cast(Sum(LAT_N) as decimal(10,2)) lat ,cast(Sum(LONG_W) as decimal(10,2)) lon from STATION;

  • sonergonul + 2 comments

    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 + 1 comment

      Works fine.

      select round(sum(LAT_N),2) as lat, round(sum(LONG_W),2) as lon
from Station
    • chenjesu + 0 comments

      It's not truncated to 2 decimal points. It ends up getting something like 196.03000000000, rather than 196.03.

  • edlaierii + 2 comments

    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 + 0 comments

      Your answer is correct! Thx!

    • bhardwajanjali2 + 0 comments

      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.

  • marinskiy + 0 comments

    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 :)

  • hbautista + 0 comments

    sql server:

    select convert(decimal(10,2),sum(LAT_N)), convert(decimal(10,2),sum(LONG_W)) from station

  • dukemaneesh + 1 comment

    SELECT ROUND(sum(LAT_N,'2')),ROUND(sum(LONG_W,'2')) FROM STATION what is wrong with this code?

    • DeepamSehgal + 0 comments

      no need of quotes around 2...

  • Gnany99 + 5 comments

    "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 + 0 comments

      use cast instead of round in ms sql server.

    • BePerfect + 0 comments

      Same here.........

    • sabiqueakhan + 1 comment

      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 + 0 comments

        its work mssql

    • prateek_11dec + 0 comments

      so helpful..

    • mohd_rafi0195 + 0 comments

      '' it is why used

  • shivu_narang + 1 comment

    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 + 0 comments

      You need to print these 2 number separated by space instead of breaking them into 2 queries.

  • abhaykumar09 + 2 comments

    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 + 1 comment

      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

    • GuramidevAsked to answer + 1 comment

      submit with mysql, there are some bugs here with oracle

      • AvmnuSngHackerRank AdminChallenge Author + 1 comment

        what kind of issue with Oracle, The problems is tested for all the options allowed to submit the query.

        • Guramidev + 3 comments

          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 AdminChallenge Author + 1 comment

            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 + 1 comment

              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 AdminChallenge Author + 0 comments

                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 + 0 comments

            SELECT ROUND( SUM( LAT_N ),2 ),ROUND( SUM( LONG_W ),2 ) FROM STATION; IT WORKS IN ORACLE

          • VishiGulati + 0 comments

            SELECT ROUND( SUM( LAT_N ),2 ),ROUND( SUM( LONG_W ),2 ) FROM STATION; IT WORKS IN ORACLE

  • jailsonevora + 0 comments

    MSQL Server

    select CAST(ROUND(sum(LAT_N), 2) AS NUMERIC(12,2)), CAST(ROUND(sum(LONG_W), 2) AS NUMERIC(12,2)) from station

Load more conversations