It’s time to learn how to manage numeric data.
Let’s learn how to use
📌COUNT: find the number of rows that match specified criteria
📌SUM: find the total sum of a numeric column
# Table of Contents
Query 1. Using COUNT
SELECT COUNT(*) FROM address; SELECT COUNT(address2) FROM address;
Query 2. Using COUNT with DISTINCT
SELECT COUNT(rental_duration) FROM film; SELECT COUNT(DISTINCT rental_duration) FROM film;
Query 3. Using SUM
SELECT SUM(length) FROM film; SELECT SUM(length)/60 AS entire_hour from film;
Q1. Finding the number of all columns and address2 columns in the address table
SELECT COUNT (column name)
Do you remember how to view all the columns in a table? We have to use
SELECT * FROM table name. If we want to see only the number of columns and not the data itself, we can use the new query
SELECT and since we want to see the number of rows (data) in all the columns, remember to use an asterisk (
SELECT COUNT(*) from address;
What if we wanted to see only the number of rows in a specific category? Then instead of an asterisk, we would write the name of the column in the parentheses. Let’s try address2:
SELECT COUNT(address2) FROM address;
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 parentheses.
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 last tutorial.
Let’s look at how we can use that with
You can count the number of all rows in rental duration using
SELECT COUNT(rental_duration). But what if you want to know the number of unique values in the column? In the practice example we used in the last tutorial, we saw the values 3, 4, 5, 6, and 7 (hours). In total, there are 5 different rental duration periods.
You can find this ‘total’ if you use
SELECT COUNT. 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:
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 films by using
SUM has the same structure we used with
COUNT. You can write
SUM right after
SELECT and the column name inside the parentheses 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, 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
🔑 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
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.