 In the tables, we have a lot of data and we have the detailed data. But sometimes it is useful to have an overview of the data, to get a summary of the data, to get an aggregate of the data. So there are powerful functions available to perform this. And of course, there is a need to remove the duplicates also. So in this module, I will cover all of this what I have mentioned. So let's look at the overview of this module summarizing functions, eliminating duplicates, grouping the results and most importantly, retrieving or pulling data from different tables from multiple tables. So let's look at this in more detail. So just as PHP contains large number of built-in functions, MySQL also gives you many functions to assist with your queries. In this module, we look at some of MySQL's aggregate functions. Rather than returning the actual data contained in a table, these functions let you summarize a table in different ways. Summarize in different ways. You can count in slightly different ways also. For example, count the field name returns the number of rows selected by the query where field name is not null. And count a static over here. Count a static over here. We can have a field name also. Returns the number of rows selected by the query regardless of whether the rows contain any null values. So here are a couple of count examples and you see the count examples over here. So we have one count example over here. We have one count example over here and this is the min over here, aggregate. So you see that we can work with the dates also. So how do we eliminate the duplicates? Occasionally a query returns more data than you actually need, even when using where and limit clauses say your access log table contains the following data. This is your access log. And what we can see over here is the member ID is repeating over here. But these URLs are different. We'll talk about it. Now imagine you want to get a list of the IDs of users that have accessed the site since November 7, you might create a query as follows. You might create a query as follows. Might. Now there's a problem over here. The value 3A pays twice in the result. Okay. This is because there are two rows in the access log table with member ID of 3 and last access later than 7 November. Representing two different pages viewed by user number 3. If you were displaying this data in a report, for example, user number 3 would appear twice. You can imagine what would happen if this was 100 times viewed. Okay. So we need to use duplicates removal. Okay. How do we do this? Distinct removes any rows that are exact duplicates of other rows from the result set. For example, the following query will still contains two instances of three in the member ID column, because the page URL column is different. Okay. Three is here, but these are different. These are different over here because of distinct one three is gone. Okay. Now how do we group the results? Very interesting. You have seen how to use functions such as count and some to retrieve overall aggregate data from a table. Such as how many female members are in the book club. What if you wanted to get more fine-grained information? For example, say you want to find out the number of different page URLs, each member has you. You might try this. You might try this query, but it is wrong. Why? It's not a good idea. All this query has given you is the total number of rows in the table. Instead, you need to group the page URL count by a member ID. To do this, you add a group by clause, which is in this over here group by a clause. This works. That's better by combining the aggregate function count with a column to group by a member ID. You can view statistics on per member basis. In this case, you can see that members one and six have each read one distinct page, whereas member three has visited two different pages. Okay, so you can group by an order by in the same query. All right, you can do this and which is shown over here. Highest number of distinct pages. You can see over here. So this is very powerful. And finally, pulling data from multiple tables. So far, your queries have worked with one table at a time. Right. However, you can do this. So far, your queries have worked with one table at a time. Right. However, real strength of a relational database that you can query multiple table at once, using a join. Okay. In the previous example that retrieved statistics from the access log table, your results sets contained a list of major member IDs in a member ID column. For instance, let's say you want to list of all members that have accessed the website. So these are the members. Without names. Without names. Now, of course, the member IDs isn't very useful. If you want to know the names of the members involved, you have to run another query to look at the data in the members table. So one, three, six, you have over here. But what about the accesses? Now you can see that the member number one is in fact, John Sparks, member three is whatever you can see. Six is Bill Swann. However, by using a join, you can combine the data in both tables to retrieve not only the list of member IDs that have accessed the site, but their names as well, all this in a single query. Okay. So over here, you have performed the join. Over here, you have the join and you have the names and access everything. All right from the access log. So this is the power of the join. So that's all I have for this module for you.