Summary

Summary of SQL Query to Calculate Average Population

In this session, the speaker explains how to calculate the average population of all cities from a database table named city using SQL. The table contains columns: ID, country code, district, and population.

Steps to Calculate Average Population

Understand the Requirement:
- We need to query the average population and round it down to the nearest integer.
Select the Population Column:
- Instead of selecting all columns with SELECT * FROM city, we focus on the population column.
Use AVG Function:
- The average is calculated using the AVG() function which sums up all the population values and divides by the count of cities.
Rounding Down the Result:
- To round down the average to the nearest integer, the ROUND() function is used, passing the result of the AVG() function.
Final Query:
- Since there are no specific filters required, the query is straightforward.

Example SQL Query

SELECT ROUND(AVG(population)) FROM city;

Conclusion

The speaker successfully formulates the SQL query to obtain the average population for all cities and confirms that the query works as intended.

Video Transcript

Hey, I will do average population from the subsection aggregation of SQL in hacker rank We're given the table city with the Collins ID country code district and population We're asked to query the average population for all cities in a table city Rounded down to the nearest integer So I'm gonna use my SQL for this. Let's get started with select star from city Now we're asked to get the average population. So instead of star, we're gonna work with population That's the column right number of people in that city So we have to take the average of all the cities that is we take all the values for a population Add them up and then we divide that by the number of Cities that we used in this case. There's a function AVG that we can use So AVG of population will do that for us Now round it down to the nearest integer if you want to ensure that Rounded you can call the round function passing this AVG of population to round parentheses AVG of population then you close the parentheses Okay, since it's all the cities we don't have any filter with where and that's just like that. Oh, I had a Additional parentheses there. Let me click submit code again And There you go. This is the average population for all cities