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?