1
00:00:00,025 --> 00:00:05,788
[SOUND] Let's talk numbers.

2
00:00:05,788 --> 00:00:09,735
In this stage, we'll cover some
new functions, keywords, and

3
00:00:09,735 --> 00:00:12,679
operators that will help
us generate reports and

4
00:00:12,679 --> 00:00:17,840
answer important questions such as,
how many books did we sell last month?

5
00:00:17,840 --> 00:00:22,640
How much revenue was generated last week,
or what's the average review for

6
00:00:22,640 --> 00:00:23,670
one of our products?

7
00:00:25,410 --> 00:00:28,820
The first function we're going
to take a look at is count.

8
00:00:28,820 --> 00:00:31,630
It can be used in many different ways.

9
00:00:31,630 --> 00:00:36,330
Some common usages are counting
all the rows in a table.

10
00:00:36,330 --> 00:00:39,830
For example,
how many uses does our service have?

11
00:00:39,830 --> 00:00:42,438
Counting the number of results in a query.

12
00:00:42,438 --> 00:00:46,178
For example,
how many products cost less than 9.99?

13
00:00:46,178 --> 00:00:50,450
Counting the unique or
distinct entries in a table.

14
00:00:50,450 --> 00:00:55,990
For example, how many distinct genres
do we have in the movies table?

15
00:00:55,990 --> 00:01:00,150
Finally, you can count aggregates or
collections of things.

16
00:01:00,150 --> 00:01:04,650
For example,
how many movies do we have in each genre?

17
00:01:04,650 --> 00:01:05,940
Let's see COUNT in action.

18
00:01:07,030 --> 00:01:12,490
Here we have a SELECT statement that
selects all uses in the database.

19
00:01:12,490 --> 00:01:17,880
To get the total number of users,
I could order by ID in a descending order

20
00:01:19,870 --> 00:01:23,460
and limit it to only
one row to be returned.

21
00:01:25,470 --> 00:01:31,280
Getting the ID could possibly reveal
the number of users in the database.

22
00:01:31,280 --> 00:01:34,510
However, what it does
actually reveal is that,

23
00:01:34,510 --> 00:01:36,860
is the number of users that's ever been.

24
00:01:37,880 --> 00:01:41,680
Imagine any number of users
deleted their accounts and

25
00:01:41,680 --> 00:01:44,840
their records were removed
from the database.

26
00:01:44,840 --> 00:01:49,330
This number isn't reflective of
the current state of the database.

27
00:01:49,330 --> 00:01:51,600
This is why we'd use count,

28
00:01:51,600 --> 00:01:56,460
to get the current number of
records in the customers table.

29
00:01:56,460 --> 00:02:00,660
We can add the count function around
the asterisks and run the query.

30
00:02:05,140 --> 00:02:07,810
Now, this is the correct number.

31
00:02:07,810 --> 00:02:12,910
You may be wondering why I'd use
a start rather then a specific column?

32
00:02:12,910 --> 00:02:15,350
Firstly, it's really quick to type.

33
00:02:15,350 --> 00:02:19,220
I could include something like ID and
Run, and

34
00:02:19,220 --> 00:02:25,370
I get the same results, but what happens
when we use last underscore name?

35
00:02:25,370 --> 00:02:28,080
The number is smaller than our total.

36
00:02:28,080 --> 00:02:29,410
Why is that?

37
00:02:29,410 --> 00:02:33,730
Count actually counts only
the rows where values are present.

38
00:02:33,730 --> 00:02:38,820
In other words,
it counts all non-null values in a column.

39
00:02:38,820 --> 00:02:41,600
If a record has no value in that column,
for

40
00:02:41,600 --> 00:02:46,610
example the last_name column,
then it won't be counted.

41
00:02:46,610 --> 00:02:52,240
Using an asterisk guarantees you'll count
all rows, regardless of its contents.

42
00:02:53,300 --> 00:02:55,930
Let's ask another question.

43
00:02:55,930 --> 00:02:59,607
How many users have
the first_name of Andrew?

44
00:03:05,138 --> 00:03:06,243
When we run this,

45
00:03:06,243 --> 00:03:10,610
we now see that it counts all of
the users with the first name of Andrew.

46
00:03:11,740 --> 00:03:15,880
The COUNT function in this instance,
is only counting the rows

47
00:03:15,880 --> 00:03:20,610
of the results of the query,
not all of the rows in the table.

48
00:03:20,610 --> 00:03:24,270
Let's see another example
in another setting.

49
00:03:24,270 --> 00:03:27,840
Let's count all of the products
in the clothing category.

50
00:03:29,320 --> 00:03:30,560
Awesome.

51
00:03:30,560 --> 00:03:35,000
I counted them by how many categories
of products we have in the database.

52
00:03:35,000 --> 00:03:39,370
First, let's select the category
from the products table.

53
00:03:45,397 --> 00:03:50,330
The results show all of the categories
of all of the rows in the table.

54
00:03:50,330 --> 00:03:52,930
In other words, there's duplicates.

55
00:03:52,930 --> 00:03:58,980
We can use a keyword to only return
unique values and discard any duplicates.

56
00:03:58,980 --> 00:04:00,640
This keyword is distinct.

57
00:04:02,500 --> 00:04:05,910
Now, I can eyeball and
count the categories, but

58
00:04:05,910 --> 00:04:09,320
I don't trust my counting
over a computer counting.

59
00:04:09,320 --> 00:04:13,250
So we can wrap the distinct
category in the count function.

60
00:04:14,710 --> 00:04:17,920
Now this returns the number
of categories we have.

61
00:04:17,920 --> 00:04:20,635
Remember counting nulls no values.

62
00:04:20,635 --> 00:04:25,464
So,if you have a category that is
set to null on one of your products,

63
00:04:25,464 --> 00:04:29,398
that apps in category won't
be included in this count.

64
00:04:29,398 --> 00:04:33,738
We know how many categories are in
the table using the distinct keyword and

65
00:04:33,738 --> 00:04:35,008
the count function.

66
00:04:35,008 --> 00:04:38,330
In the next video, we'll take a look
at counting groups of gross for

67
00:04:38,330 --> 00:04:41,030
example, how many products
are in each category?