 Hello everyone, I am Mrs. Sunita Do. The topic covered is Structured Query Language Aggregate Function. In this video, the topics Aggregate Function, Group Buy and Having Clause are covered. Learning outcome. At the end of this session, students will be able to write the query for given problem statement using Aggregate Function like Average, Mean, Max etc. and write the query using Group Buy and Having Clause. Aggregate functions are the functions that take a collection of values as input and return a single value. SQL offers 5 built-in aggregate function, average, minimum, maximum, total and count. The input to sum and average must be a collection of number, but other operators can operate on collection of non-numeric data type such as strings, syntax, select aggregate function name in bracket, column name, from, table name, where, condition. Example. Find the Average Account Balance at Brinton Branch. So, relation required is account which consists of 3 fields, account number, branch name and balance. Aggregate function required is Average. As we have to find out the Average Account Balance at the Brinton Branch, we apply the Average function to the field balance where the branch name is Brinton. There are 2 tuppers with the branch name Brinton with the balance 900 and 750. So, the Average function is applied to the balance of the Brinton Branch and the result contains 825. So, the query for this example is Select Average in Bracket Balance from Account where branch name is equal to Brinton. Second example. Find the number of tuppers in the customer relation. So, the relation required is customer and the aggregate function required is count. Here, the aggregate function count is applied to the relation customer. The result contains the number of tuppers in the relation customer that is 12. So, the query can be written as Select Count star from the customer. Consider third example. Find the number of depositors in the bank. So, the relation required is depositor and the aggregate function required is count. Here, to find the number of depositors in the bank, we apply the aggregate function count to the field customer name of the relation depositor. The relation consists of 7 tuppers but the result will consider distinct customer name and returns the 6 tupper. Query can be written as Select Count in Bracket Distinct Customer Name from Depositor. The keyword distinct is used for the elimination of duplicates. If we omit the distinct keyword from the query, then the result contains all the tuppers of the depositor relation that is 7 tupper. For example, find the average balance for all accounts. Here we have to apply the aggregate function average to the field balance and it will return the average of balance as a result. So, query can be written as Select Average Balance from Account. Query for the same example using the average formula can be written as Select Sum in Bracket Balance divided by 7 from Account and the result is 614 group by clause. Group by clause is set is used to apply the aggregate function not only to a single set of tuppers but also to a group of set of tuppers. The attribute or the attributes given in the group by clause are used to form groups. Tuppers with the same value on all the attributes in the group by clause are placed in one group. Syntax select column 1, column 2 from table name where condition group by column 1, column 2 order by column 1, column 2. Square bracket indicate the optional clause. So, in this syntax where and order by clause are optional. Consider the example of the group by clause find the average account balance at each branch. So, the relation required is account and the aggregate function is average. The relation account consists of account number, branch name and the balance. As we have to find out the average account balance at each branch. So, we apply the aggregate function average to the group of branches. There are two tuppers with the branch name Brenton. So, we apply the average function to these two tuppers. The result contains the average account balance at each branch as shown on the slide in the result of query. The query for this example can be written as select branch name comma average in bracket balance from account group by branch name. Consider second example find the number of depositor for each branch. Two relations that is the depositor and the account. So, our attribute is account number as we have to find the number of depositor for each branch. So, we apply the aggregate function count to the field customer name and group by to the field branch name. The result is shown on the slide in the result of query. The query for this example can be written as select branch name comma count in bracket distinct customer name from depositor comma account where depositor dot account number is equal to account dot account number group by branch name. Till now we consider the various aggregate function. Now pause this video and reflect on these question for a minute or two minute and write your response. Once you return the answer to this question then you can restart playing this video. So, the question is what is the output of the following query for the relation account? I hope all of you have completed this activity. Here mean and max aggregate functions are applied to the balance and the group by clause to the branch name. Minimum balance at the branch downtown is 350 out of 4 tuples, tuple number 2567. Minimum balance at the branch perirage is 400 out of 3 tuples 13 and 4. Maximum balance at the branch downtown is 750 out of 4 tuples 256 and 7. And the maximum balance at the branch perirage is 900 out of 3 tuples 13 and 4. Having clause, having clause is used when the condition applies to each group constructed by the group by clause. What here is predicate in the having clause are applied after the formation of group whereas the predicate in the where clause are applied before forming the groups. So, the syntax for the having clause is select column name from table name where condition group by column names having condition order by column name. The having clause must follow the group by clause in a query and must also present the order by clause if used. Example of having clause, find the name of all the branches where the average account balance is more than dollar seven hundred. For this example, we consider the relation account. As we have to find the name of all the branches where the average account balance is more than dollar seven hundred. So, first we have to apply the group by clause to the field branch. So, there are two tuples for the branch name brine turn. Here the average balance is calculated for the branch brine turn and for remaining branch name average of the balance is return as balance. From this relation we have to find out the name of all the branches where the average account balance is more than dollar seven hundred. So, the result contains only one tuple with the branch name brine turn and the average balance is eight twenty five. The query for this example is select branch name comma average of balance from account group by branch name having average in bracket balance greater than seven hundred. These are references. Thank you.