In this lesson, we'll delve into the basics of SQL functions, with a particular focus on the COUNT function. This function serves as a convenient tool when conducting quantitative analysis of our data set, such as determining the total number of movies or characters in a table. The COUNT function in SQL is crucial for summarizing data, allowing you to count rows in a table based on certain criteria. Let's get started.
In the previous course, we used a simplified Marvel movies dataset in the lessons, and used an expanded dataset in the practice tasks. In this lesson, we will switch to using the expanded dataset in the lesson. Let's take a look!
Movies Table
Plain text1 movie_id | movie_name | release_date | phase 2----------+---------------------------------------------+--------------+------- 3 1 | Iron Man | 2008-05-02 | 1 4 2 | The Incredible Hulk | 2008-06-13 | 1 5 3 | Iron Man 2 | 2010-05-07 | 1 6 4 | Thor | 2011-05-06 | 1 7 5 | Captain America: The First Avenger | 2011-07-22 | 1 8 6 | The Avengers | 2012-05-04 | 1 9 7 | Iron Man 3 | 2013-05-03 | 2 10 8 | Thor: The Dark World | 2013-11-08 | 2 11 9 | Captain America: The Winter Soldier | 2014-04-04 | 2 12 10 | Guardians of the Galaxy | 2014-08-01 | 2 13 11 | Avengers: Age of Ultron | 2015-05-01 | 2 14 12 | Ant-Man | 2015-07-17 | 2 15 13 | Captain America: Civil War | 2016-05-06 | 3 16 14 | Doctor Strange | 2016-11-04 | 3 17 15 | Guardians of the Galaxy Vol. 2 | 2017-05-05 | 3 18 16 | Spider-Man: Homecoming | 2017-07-07 | 3 19 17 | Thor: Ragnarok | 2017-11-03 | 3 20 18 | Black Panther | 2018-02-16 | 3 21 19 | Avengers: Infinity War | 2018-04-27 | 3 22 20 | Ant-Man and The Wasp | 2018-07-06 | 3 23 21 | Captain Marvel | 2019-03-08 | 3 24 22 | Avengers: Endgame | 2019-04-26 | 3 25 23 | Spider-Man: Far From Home | 2019-07-02 | 3 26 24 | Black Widow | 2021-07-09 | 4 27 25 | Shang-Chi and the Legend of the Ten Rings | 2021-09-03 | 4 28 26 | Eternals | 2021-11-05 | 4 29 27 | Spider-Man: No Way Home | 2021-12-17 | 4 30 28 | Doctor Strange in the Multiverse of Madness | 2022-05-06 | 4 31 29 | Thor: Love and Thunder | 2022-07-08 | 4 32 30 | Black Panther: Wakanda Forever | 2022-11-11 | 4 33 31 | Ant-Man and The Wasp: Quantumania | 2023-02-17 | 5 34 32 | Guardians of the Galaxy Vol. 3 | 2023-05-05 | 5 35 33 | The Marvels | 2023-11-10 | 5 36(33 rows)
The movies table includes the first 33 Marvel movies. Each entry in the table includes a value for movie_id, movie_name, release_date and phase. The movie_id column corresponds to the movie_id column in the movie_details and characters tables.
Movie Details Table
Plain text1 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 2----------+--------------------+------------------------+-------------+----------------- 3 1 | 140 | 585.2 | 7.9 | 126 4 2 | 150 | 263.4 | 6.7 | 112 5 3 | 200 | 623.9 | 7.0 | 124 6 4 | 150 | 449.3 | 7.0 | 115 7 5 | 140 | 370.6 | 6.9 | 124 8 6 | 220 | 1519.6 | 8.0 | 143 9 7 | 200 | 1215.4 | 7.2 | 130 10 8 | 170 | 644.6 | 6.9 | 112 11 9 | 170 | 714.3 | 7.7 | 136 12 10 | 170 | 773.3 | 8.0 | 121 13 11 | 250 | 1405.4 | 7.3 | 141 14 12 | 130 | 519.3 | 7.3 | 117 15 13 | 250 | 1153.3 | 7.8 | 147 16 14 | 165 | 677.7 | 7.5 | 115 17 15 | 200 | 863.8 | 7.6 | 136 18 16 | 175 | 880.2 | 7.4 | 133 19 17 | 180 | 850.8 | 7.9 | 130 20 18 | 200 | 1346.9 | 7.3 | 134 21 19 | 321 | 2048.4 | 8.4 | 149 22 20 | 162 | 622.7 | 7.1 | 118 23 21 | 175 | 1128.3 | 6.9 | 123 24 22 | 356 | 2797.8 | 8.4 | 181 25 23 | 160 | 1131.9 | 7.5 | 129 26 24 | 200 | 378.5 | 6.8 | 134 27 25 | 200 | 430.0 | 7.6 | 132 28 26 | 200 | 402.9 | 6.8 | 157 29 27 | 260 | 1995.4 | 8.4 | 148 30 28 | 180 | 1594.7 | 7.8 | 132 31 29 | 200 | 714.3 | 7.5 | 130 32 30 | 250 | 859 | 7.3 | 161 33 31 | 200 | 476 | 6.2 | 125 34 32 | 250 | 845 | 8.1 | 150 35 33 | 250 | 200 | 6.1 | 124 36(33 rows)
The movie_details table includes details for the first 33 Marvel movies. Each entry in the table includes a value for movie_id, budget_million_usd, box_office_million_usd, imdb_rating, and runtime_minutes. The movie_id column corresponds to the movie_id columns in the movies and characters table.
Characters Table
Plain text1 character_id | movie_id | character_name | actor | screen_time_minutes 2--------------+----------+-------------------------------------+------------------------+--------------------- 3 1 | 1 | Tony Stark | Robert Downey Jr. | 120 4 2 | 1 | Pepper Potts | Gwyneth Paltrow | 40 5 3 | 1 | James Rhodes | Terrence Howard | 30 6 4 | 1 | Obadiah Stane | Jeff Bridges | 25 7 5 | 1 | Happy Hogan | Jon Favreau | 20 8 6 | 1 | Agent Coulson | Clark Gregg | 15 9 7 | 1 | Raza | Faran Tahir | 10 10 8 | 1 | Yinsen | Shaun Toub | 10 11 9 | 2 | Bruce Banner/Hulk | Edward Norton | 110 12 10 | 2 | Betty Ross | Liv Tyler | 35 13 11 | 2 | Thaddeus Ross | William Hurt | 25 14 12 | 2 | Emil Blonsky/Abomination | Tim Roth | 20 15 13 | 2 | Leonard Samson | Ty Burrell | 15 16 14 | 2 | General Ross | William Hurt | 15 17 15 | 2 | Jack McGee | Tim Blake Nelson | 10 18 16 | 3 | Natasha Romanoff/Black Widow | Scarlett Johansson | 100 19 17 | 3 | Nick Fury | Samuel L. Jackson | 30 20 18 | 3 | James Rhodes/War Machine | Don Cheadle | 25 21 19 | 3 | Ivan Vanko/Whiplash | Mickey Rourke | 20 22 20 | 3 | Justin Hammer | Sam Rockwell | 15 23 21 | 3 | JARVIS | Paul Bettany | 10 24 22 | 3 | Howard Stark | John Slattery | 10 25...... 26(243 rows)
The characters table contains 243 entries for characters that appear in the first 33 Marvel movies. Each entry has a character_id, movie_id, character_name, actor, and screen_time_minutes. The movie_id column corresponds with the movie_id column from the movies and movie_details table.
Now let's dive into the main content of the lesson. The COUNT function in SQL is used to count the number of rows in a table. The basic syntax is as follows:
SQL1SELECT COUNT(column_name) 2FROM table_name;
Here’s a breakdown of the syntax:
SELECT: This keyword is used to select data from a database.COUNT(column_name): This function counts the number of entries in the specified column.FROM table_name: This specifies the table from which to count the entries.
The COUNT function can be used to count all rows or rows that meet specific criteria (using the WHERE clause).
Let's start with a simple example where we count all entries in the characters table.
SQL1SELECT COUNT(*) FROM characters;This command counts all rows in the characters table, regardless of their content. Executing this query will produce the following output:
Plain text1 count 2------- 3 243 4(1 row)
The output tells us that there are 243 rows in the characters table.
Now, let's move on to conditional counting. Suppose we want to find the number of movies in phase 3. The SQL query is:
SQL1SELECT COUNT(*) FROM movies
2WHERE phase = 3;This command counts the rows in the movies table where the phase column's value is 3. Executing this query will produce the output:
Plain text1 count 2------- 3 11 4(1 row)
Using the COUNT function, we know that phase 3 has 11 movies.
Awesome work! In this lesson, we covered:
- The basics and syntax of the
COUNTfunction - Using the
COUNTfunction on tables - Combining
COUNTwith theWHEREclause to perform quantitative analysis
Understanding and effectively using the COUNT function enables you to perform essential quantitative analysis and extract valuable insights from your datasets. Now, you're ready to tackle more advanced practice exercises. Happy coding!