Summary

Summary of Weather Observation Station 2 Problem

In this lesson, we solve the Weather Observation Station 2 problem from HackerRank under the SQL category focusing on aggregation. The task involves summing the values in two columns: lat_n and long_w, then rounding the results to two decimal places.

Steps to Solve the Problem

Select the Table: Start by selecting data from the table named station.
Use the SUM Function:
- To compute the sum of lat_n, use:
```
SUM(lat_n)
```
- To compute the sum of long_w, use:
```
SUM(long_w)
```
Round the Results:
- The results need to be rounded to two decimal places. We use the ROUND function:
```
ROUND(SUM(lat_n), 2)
```
```
ROUND(SUM(long_w), 2)
```

Final Query Structure: Here's how the full SQL query should look:

SELECT 
    ROUND(SUM(lat_n), 2) AS total_lat_n,
    ROUND(SUM(long_w), 2) AS total_long_w
FROM 
    station;

Execute the Query: Run the query to get the desired results, which will display the summed values for both columns rounded to two decimal places.

By following these steps, we can efficiently obtain the required sums for the latitude and longitude columns as stipulated in the problem.

Video Transcript

This lesson I'll solve the problem weather observation station 2 from the subsection aggregation in the category SQL from HackerRing. So we're given this stable station as the calls ID city state lat underscore n long underscore w. So we're asked to sum up all the values in lat underscore n and then also show the sum of all the values in long underscore w. And we must also round it to two decimal places. So keep in mind those things. So first how can we get the sum? That's going to be very simple. I'm going to use the function sum from my SQL. Others flavors of SQL might have something similar. So you can probably try the same thing. Call it in the select section of the query. You're going to say sum parentheses and then you pass the column name. In this case we are summing lat underscore n and then for the second column we do the same sum except we change to the different column long underscore w. And that's going to give us what is desired except it's not going to be rounded to two decimal places. That's when round comes in. That's the next function we're going to use. We're going to get that sum statement and place that into the round function and then we're going to do comma the number of decimal places. In this case the documentation here is the D as you can see from these examples. So let's go. So I'm going to choose my SQL. So we're going to select something from name of the table station. Now this something let's fill it out. So we have lat underscore n and we have long underscore w. So these are the two columns. So we want to take the sum of the lat underscore n. Simply call the sum function like so. Pass lat underscore n as the argument here in the select statement. Do the same for long underscore w. Call sum pass long underscore w as the argument. If you run this code you're going to get the sum for all the lat underscore n as the first column and then the sum for all the values on long w as the second column. As you can see here this is the sum. If you take all the values for lat underscore n and add them up you're going to get this. And if you take all the values for long underscore w you're going to get that. Now we need to round it to only two decimal places. In this case there's more than two. So that's when the round function comes in. So you're going to go here whenever you have this sum of whatever you think of that as one thing that is to be passed to the round function. So you can write round parentheses past this thing as sum as the argument comma and you need to pass the second argument to the round function that will tell it how many decimal places you want to round it. In this case it's two by the requirements of the problem. So we're going to pass comma two there. Do the same for the sum of long underscore w. You're going to see round function call that pass the sum of long underscore w comma second argument of round is the number of decimal places, place a two there. And then run code again and see if it works. As you can see now we only have two decimal places that is there's two jays to the right of the dot. So click submit code and that's how we can solve whether observation station two.