Counting Results

[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