[SQL Basic] What is the total length of all the films added together? — SQL COUNT/SUM
#9. Marketing/Data Analysis for Beginners
It’s time to learn how to manage numeric data.
But first, here’s our glossary:
Lesson 3:
📌SELECT: you’re asking the database to show you something
📌FROM: you’re giving the database a location to search
📌WHERE: you’re narrowing down/specifying the location
Lesson 4:
📌ORDER BY: you’re asking for a specific order for the list to be displayed
Lesson 5:
📌AND: find information that matches 2 specific categories
📌OR: find all information that matches at least 1 specified category
📌IN: find information that matches any of the following categories
Lesson 6:
📌LIKE: used to find a specific pattern in a column
Lesson 7:
📌AS: used to rename columns
📌DISTINCT: used to see unique values
📌IS NULL: used to see null values
🆕 🌟
📌COUNT: find the number of rows that matches specified criteria
📌SUM: find the total sum of a numeric column
Let’s learn how to use COUNT and SUM.
# Table of Contents
Query 1. Finding the number of all columns and address2 columns in the address table
SELECT COUNT(*) FROM address; SELECT COUNT(address2) FROM address;Query 2. Finding the number of all columns and unique columns of rental duration in the film table
SELECT COUNT(rental_duration) FROM film; SELECT COUNT(DISTINCT rental_duration) FROM film;Query 3. Finding the total length of all films in the film table
SELECT SUM(length) FROM film; SELECT SUM(length)/60 AS entire_hour from film;# COUNT
Q1. Finding the number of all columns and address2 columns in the address table
SELECT COUNT (column name)
Do you remember how to open all the columns in a table? We have to use SELECT * FROM table name to view all the data. If we want to see only the number of data and not the data itself, we can use the new query COUNT.
We can add COUNT after the SELECT and write the column name between the parenthesis to see the number of columns. And if you want to see the number of all the columns, remember to use the asterisk (*).
SELECT COUNT(*) from address;
What if we wanted to see only the number of columns in a specific category? Then instead of asterisk, we would write the name of the column in the parenthesis. Let’s try address2:
SELECT COUNT(address2) FROM address;
Using COUNT(*) calculates the number of columns including the null value. If you want to know the number of columns without the null value, you have to write a specific column name inside the parenthesis.
Q2. Finding the number of all columns and Unique columns of rental duration in the film table
SELECT COUNT (DISTINCT column name)
If we want to see the number of unique values in a column, we can use the DISTINCT command that we learned in the 7th tutorial.
Let’s look at how we can use that with COUNT.
You can count the number of all columns of rental duration using SELECT COUNT(rental_duration). But what if you want to know the number of unique values in the columns? In the practice example we used in the 7th tutorial we saw the values of 3, 4, 5, 6, and 7. In total, there are 5 different rental duration periods.
You can find this ‘total’ if you use DISTINCT with SELECT COUNT. It is the same structure but you can see the unique value by adding DISTINCT in the parenthesis. So you can see the result of 5 by using this statement:
SELECT COUNT(DISTINCT rental_duration) FROM film;
If you don’t use the DISTINCT query, you will get the total count of all the different rental duration periods. Take a look at the picture below for a comparison:
# SUM
Q3. Finding the total length of all films in film table
SELECT SUM (column name)
Now we will try finding the total length of all the films by using SUM.
SUM has the same structure we used with COUNT. You can write SUM right after SELECT and the column name inside the parenthesis like this:
SELECT SUM(length) FROM film;
You can see the number “115272” in the results, but we don’t know yet how many hours that equals to since the value shown is in minutes. So let’s divide its value into 60 to see it by hours and add a new column name using AS.
🔑 We can use the symbols (+), (-), (*), ( /) in SQL when the data is numeric. So we can see the total length of all films by using SUM(length)/60. Just add AS entire_hour to rename the new column of the final result.
SELECT SUM(length)/60 AS entire_hour FROM film;
# Practice Time
Your challenge today is to find the number of Unique Values of replacement_cost in the film table. It’s pretty simple, so let’s use what we learned so far to find the values!
We have to use COUNT and DISTINCT together to see the number of unique values. If we only use DISTINCT here, we have to count the result manually so it is better to use COUNT to make things easier.
SELECT COUNT(DISTINCT replacement_cost) FROM film;
The result is 21 → meaning we have 21 different values (in this case, 21 different prices for replacing the movies) in the specified column.
If you need some visuals, follow our video tutorial linked below:
# Tutorial Video
# [SQL Basic Series] — Marketing/Data Analysis for Beginners
1. What is SQL? — Database Introduction
2. Downloading SQLGate and Connecting to a Database
3. What kind of data is in the ‘film’ table? — SELECT/FROM/WHERE
4. How to sort film lists by price (rental rate)? — ORDER BY
5. Can I rent a 3 hour movie for $0.99? — AND/OR/IN
6. How can I find a movie that’s thrilling? — LIKE
7. How to rename columns and see unique values? — AS/DISTINCT/IS NULL
8. Let’s see Sample Database tables!
9. What is the total length of all the films added together? — COUNT/SUM
10. What is the average film length? — AVG/MIN/MAX
11. How many movies have the same rating? — GROUP BY/HAVING
12. Let’s designate a value based on the rental duration. — CASE
13. Let’s find out the movie genres by joining tables! — INNER JOIN
14. Let’s check movie rentals by the inventory list! — LEFT/RIGHT JOIN
15. Let’s find customers that have rented movies priced $9.99! — Subquery
16. How to work with Date Functions in SQL — DATE_FORMAT, DATEDIFF
17. How to work with String Functions in SQL — CONCAT, LENGTH, SUBSTR
18. Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD
19. Let’s learn some advanced CASE queries!
20. Let’s learn some advanced JOIN queries!