It’s time to learn how to manage numeric data.
Let’s learn how to use COUNT and SUM.

🆕 🌟
📌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;

# COUNT

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 COUNT.

Add COUNT after 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 COUNT.

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 DISTINCT with 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:

# 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 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 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 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.