Lesson 30

Courses / HackerRank SQL Problem Solving Solution Challenges

Revising Aggregations - The Count Function | SQL Aggregation | HackerRank Solution

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Summary

Lesson Summary: SQL Aggregation and Count Function

In this lesson, we revisited the aggregation functions in SQL, particularly the COUNT function, focusing on a practical example using the city table.

Objective

We aimed to count the number of cities in the city table that have a population greater than 100,000.

Steps Undertaken

Initial Query: We started by retrieving all rows and columns from the city table using:
```
SELECT * FROM city;
```
Filtering Data: To focus only on cities with populations greater than 100,000, we added a WHERE clause:
```
WHERE population > 100000;
```
Counting Rows: To count the number of qualifying cities, we used the COUNT function:
```
SELECT COUNT(*) FROM city WHERE population > 100000;
```
Outcome: Upon executing the query, we determined that there are six cities in the database with populations exceeding 100,000.

Conclusion

This lesson illustrated the application of the COUNT function in SQL while performing a simple aggregation task based on population criteria.

Video Transcript

Hey everybody, welcome to another lesson. I will be doing revising aggregations to count function from the subsection aggregation of SQL in Hack her ring. We're asked to count the number of cities in the table city that has a population larger than 100,000. Now for the purposes of this question, we're going to treat each row where considered to be one city, so we don't care about being distinct here. We can do DB2 of my SQL is going to be the same solution. Let me get started here. I'll just start off with select star from city. So this would take all the rows and all the columns from the table. Okay, so we have the last column is a population. We have to eliminate everything that's under or equal to 100,000. So we're going to go here and we add a where and then the column is going to be population greater than 100,000. Now for the select, we need to give account of how many cities there are. How many cities that's simply the count of rows. If you want to count the number of rows, simply say count, parentheses star. It doesn't matter which column in this case. So I just leave a star. So let's submit the code. And there you go. There are six cities.