Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Counting the number of rows is great for answering questions such as "How many users do we have?" and "How many sales happened this week?".
To count rows you can use the COUNT() function.
SELECT COUNT(*) FROM <table>;
To count unique entries use the DISTINCT keyword too:
SELECT COUNT(DISTINCT <column>) FROM <table>;
Cheat Sheets
[SOUND] Let's talk numbers.
0:00
In this stage, we'll cover some
new functions, keywords, and
0:05
operators that will help
us generate reports and
0:09
answer important questions such as,
how many books did we sell last month?
0:12
How much revenue was generated last week,
or what's the average review for
0:17
one of our products?
0:22
The first function we're going
to take a look at is count.
0:25
It can be used in many different ways.
0:28
Some common usages are counting
all the rows in a table.
0:31
For example,
how many uses does our service have?
0:36
Counting the number of results in a query.
0:39
For example,
how many products cost less than 9.99?
0:42
Counting the unique or
distinct entries in a table.
0:46
For example, how many distinct genres
do we have in the movies table?
0:50
Finally, you can count aggregates or
collections of things.
0:55
For example,
how many movies do we have in each genre?
1:00
Let's see COUNT in action.
1:04
Here we have a SELECT statement that
selects all uses in the database.
1:07
To get the total number of users,
I could order by ID in a descending order
1:12
and limit it to only
one row to be returned.
1:19
Getting the ID could possibly reveal
the number of users in the database.
1:25
However, what it does
actually reveal is that,
1:31
is the number of users that's ever been.
1:34
Imagine any number of users
deleted their accounts and
1:37
their records were removed
from the database.
1:41
This number isn't reflective of
the current state of the database.
1:44
This is why we'd use count,
1:49
to get the current number of
records in the customers table.
1:51
We can add the count function around
the asterisks and run the query.
1:56
Now, this is the correct number.
2:05
You may be wondering why I'd use
a start rather then a specific column?
2:07
Firstly, it's really quick to type.
2:12
I could include something like ID and
Run, and
2:15
I get the same results, but what happens
when we use last underscore name?
2:19
The number is smaller than our total.
2:25
Why is that?
2:28
Count actually counts only
the rows where values are present.
2:29
In other words,
it counts all non-null values in a column.
2:33
If a record has no value in that column,
for
2:38
example the last_name column,
then it won't be counted.
2:41
Using an asterisk guarantees you'll count
all rows, regardless of its contents.
2:46
Let's ask another question.
2:53
How many users have
the first_name of Andrew?
2:55
When we run this,
3:05
we now see that it counts all of
the users with the first name of Andrew.
3:06
The COUNT function in this instance,
is only counting the rows
3:11
of the results of the query,
not all of the rows in the table.
3:15
Let's see another example
in another setting.
3:20
Let's count all of the products
in the clothing category.
3:24
Awesome.
3:29
I counted them by how many categories
of products we have in the database.
3:30
First, let's select the category
from the products table.
3:35
The results show all of the categories
of all of the rows in the table.
3:45
In other words, there's duplicates.
3:50
We can use a keyword to only return
unique values and discard any duplicates.
3:52
This keyword is distinct.
3:58
Now, I can eyeball and
count the categories, but
4:02
I don't trust my counting
over a computer counting.
4:05
So we can wrap the distinct
category in the count function.
4:09
Now this returns the number
of categories we have.
4:14
Remember counting nulls no values.
4:17
So,if you have a category that is
set to null on one of your products,
4:20
that apps in category won't
be included in this count.
4:25
We know how many categories are in
the table using the distinct keyword and
4:29
the count function.
4:33
In the next video, we'll take a look
at counting groups of gross for
4:35
example, how many products
are in each category?
4:38
You need to sign up for Treehouse in order to download course files.
Sign up