Hello coders, today we are going to solve Weather Observation Station 2 HackerRank Solution in SQL.
Problem
Query the following two values from the STATION table:
- The sum of all values in LAT_N rounded to a scale of 2 decimal places.
- The sum of all values in LONG_W rounded to a scale of 2 decimal places.
Input Format
The STATION table is described as follows:
| Field | Type |
|---|---|
| ID | NUMBER |
| CITY | VARCHAR2(21) |
| STATE | VARCHAR2(2) |
| LAT_N | NUMBER |
| Long_W | NUMBER |
where LAT_N is the northern latitude and LONG_W is the western longitude.
Output Format
Your results must be in the form:
lat lon
where lat is the sum of all values in LAT_N and lon is the sum of all values in LONG_W. Both results must be rounded to a scale of 2 decimal places.
Solution – Weather Observation Station 2 in SQL
MySQL
select round(sum(lat_n), 2), round(sum(long_w), 2) from station;
Disclaimer: The above Problem (Weather Observation Station 2) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.
This is the correct query.
select cast(round(sum(lat_n), 2) as decimal(10,2)),cast(round(sum(long_w), 2) as decimal(10,2))from station;
Do you know the reason of that why we need to have cast on top of round?
the syntax that he is using is T-SQL (Microsoft SQL Server)
When you ROUND() with T-SQL, it leaves trailing 0’s after the rounded number.
e.g. 120.02 vs 120.020000 where the latter answer is not correct.
He casts it as a DECIMAL(10,2) because the “2” will ensure that there will only be 2 digits after the decimal point.
select convert(decimal(10,2),sum(lat_n)),convert(decimal(10,2),sum(long_w)) from station
this also work
select sum(round(lat_n, 2)),
sum(round(long_w, 2))
from station
what is the reason behind 10 being used with 10? pls explain
In MS SQL :
SELECT (CONVERT(DECIMAL(20,2),SUM(LAT_N))) AS lat, (CONVERT(DECIMAL(20,2),SUM(LONG_W))) AS lon FROM STATION