Loading
Lesson 32
Courses / HackerRank SQL Problem Solving Solution Challenges
Revising Aggregations - Averages | SQL Aggregation | HackerRank Solution

Summary

# Lesson Summary: SQL Aggregations - Average Population Calculation

In this lesson, we revisited SQL aggregations, focusing on how to calculate averages, specifically for cities in California. The dataset we are working with includes the following columns: ID, name, country code, district, and population.

## Task
We need to query the average population of cities where the district is "California".

### Steps to Achieve the Query
1. **Filter by District**: We will filter the cities by ensuring the district value is "California".
2. **Calculate Average Population**: We will use the average function (AVG) to compute the mean of the population for the filtered cities.

### Understanding Average
- The average is calculated by summing all the elements and dividing by the total number of elements \((n)\):
  
  \[
  \text{Average} = \frac{\sum_{k=1}^{n} a_k}{n}
  \]

- For example, if you have three values \(a, b, c\), the average would be:
  
  \[
  \text{Average} = \frac{a + b + c}{3}
  \]

### SQL Query Execution
To formulate our query:
```sql
SELECT AVG(population) 
FROM city 
WHERE district = 'California';
  • This query selects the average of the population column from the city table where the district equals "California".

Result

After executing the query, the calculated average population for cities in California is approximately 113,000.67.

Conclusion

This lesson provided a concise overview of how to filter data and calculate averages using SQL. We reaffirmed the basic concept of averaging and demonstrated it with a specific example.

Video Transcript

Hey everybody, welcome to another lesson. I will do revising aggregations, averages, for the self-section aggregation of the SQL section in HackerRank. We have this stable city with the column ID name, country code, district and population. And we're asked to query the average population in all the cities where district is California. So for this, we're going to filter first by district having the value of California. And then for those rows, we're going to take all those values and take the average, the values for population. And if you don't remember what the average is, here's some brief reminder. You're just going to take the sum of the elements and divide by the number of elements. In this case, if you have a sum of ak with k being from 1 to n, and then n has to be greater than or equal to 1, so you take the sum and divide by n. So if you want a more expanded version without the sum notation, this is pretty much you have a value plus an order plus an order up to n values and then divided by n. And here's a concrete example. If you have three values, you just sum them and divide by the total number of values. In this case, a plus b plus c, take that sum and then divide by 3. In any case, this will work for dv2 and my SQL at least. So let's get started. We're going to do select star from city. And then from this query, we're going to change it so it becomes what the solution wants. So first, let's make sure it's just the rows for Californians. So we're going to add a where. The district column value is equal to California. And then we have to take the average of population. So we're going to work with the column select population here. And then we're going to use the average function AVG and take that and pass population as the argument. And that will take all the values for all these rows from California and take the population values add them up and then divide by the total number of rows or values. Let's click submit code and see what we get. And here's the answer. We got 113,000.667. And that's revising aggregations averages.
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work: