Hello everyone, welcome back. In this presentation, we will focus on aggregate functions in SQL. At first, let's focus on the theoretical aspects about aggregate functions. Basically, these aggregate functions perform calculations on multiple values. Let's say, if we have a group of values, then aggregate functions takes these group of values as an input and then it returns a single value as an output. You may be wondering why we need these kind of functions which takes multiple values and return a single value. So we will understand the need for aggregate functions now. I'll give you two different scenarios. When we are using Google Maps and we have set our source and destination and we are traveling. We can notice the estimated time, the ETA to reach the destination. How this estimated time is calculated? The Google Maps server collects data from various sources and it aggregates all the values and later it provides us with a single ETA value.
So basically, this single ETA value is not decided based on one value. So a group of values are taken and Google Maps server performs calculation on these group of values and it provides us a single ETA value. I'll give you one more real-time situation also. Let's say there is a chemical plant and there is a sensor installed in the chemical plant to note the temperature of the chemical plant. Let's assume if the temperature of the chemical plant exceeds beyond the threshold, then it should notify someone about this situation. In order to carry out this operation, the temperature sensor reads the temperature value of the plant and then it stores all the values in the database. Now the problem is, for every millisecond, temperature will be obviously varying, right? Maybe this particular second it may be 38.2, the next second it may be 38.3 or 38.1 or even anything. But the biggest problem is, if the sensor stores all the values into the database, soon the database memory will exhaust. So what can be done effectively? Instead of storing all the values, we can aggregate the value into a single value and store it in the database. From this we can understand that aggregate functions are needed for day-to-day activities and it actually takes multiple values and returns a single value. And the important thing about aggregate function is, it ignores null values except for the count function. Anyway, when we see some examples, I will explain about this. And the power of this aggregate functions can be enhanced with Group By and Having clause. Don't worry about this Group By and Having clause. In the next presentation, we are going to exclusively focus on Group By and Having clause in SQL.
So we are done with the basics of aggregate functions. Let's see the various aggregate functions in SQL. There are five different aggregate functions in SQL. Number one, count; number two, sum; number three, average; number four, minimum; and number five, maximum. We'll start with the first function, the count function. Talking about the count function, this function returns the number of rows in a database table. For example, we have a database and this database may contain end number of tables. When we apply this count function for a table, it returns the number of rows. Will it be applied on any data type? Actually, count can work on both numeric and non-numeric data type. Let's say there is a table, the name of the table be student, and it has ID, name, age, salary, and gender as the column. And let's say this table is populated with some data. Let's assume there is a user and this user is providing a query, select, he's using an aggregate function count, we need parenthesis because this is a function, and here inside this parenthesis, let's assume this user is mentioning asterisk. It means all. From student.
Now what this query will return? This query will return the number of rows as the output. How many rows are here? 1, 2, 3, and 4. So this query will return the output as four. If you see, this count aggregate function takes a group of values or multiple values and it returns a single value which is four. Let's see the variation of this count function. Let's assume this user is providing a different command. Select count in parenthesis age from student. So what the user is mentioning is, he wants to count how many values are there in the age column. In this table, student, that is this table student contains an age column or an age attribute, and this may contain end number of values. So what the user is going to do? He's going to count how many values are there in age. We can clearly notice that 1, 2, 3, and four values are there. So the output is going to be obviously four. But the intention of user is, he wanted to know how many unique values are there in the age column. Let's see. 25 is repeated twice, right? So how many unique values are there? 25, 24, and 32. Only three unique values are there. In order to achieve this, user should not use query like this, rather, the user should use the command like this. Select, count, the aggregate function, in parenthesis, distinct age, the column what he wants, and the distinct keyword. From the table name which is student here.
So when he gives this query, obviously the result will be three because how many unique values are there in age column? Three unique values. What are they? 25, 24, and 32. I'm mentioning the word unique for easy understanding, but in SQL we have a separate keyword called unique to maintain the uniqueness in the column. So what I mention here as unique is for distinct values only. I'm not talking about the unique constraint. So this is about the first aggregate function count. Let's move on to the second aggregate function sum. The name itself says that it's going to sum the entire column. So it returns the sum of the selected columns. As the name says, it's going to produce the summation as the result. So obviously, it cannot work on all data types. Rather, this sum works only on numeric data type. Let's take the same table which we have used in the previous example, the same student table. Let's say there is a user. If the user wants to get the summation of a specified column. Let's say the user is giving a query, select sum of salary. Sum, the aggregate function, in parenthesis, he's mentioning the column name as an input or a parameter.
So sum function takes column name as a parameter from the table name. Which column the user is referring? The salary column. There may be N rows in this table. To be precise, there may be multiple values in this column salary. But what the user wants? He wants the summation of the entire salary column. So when he provides this query, he will be getting the result, the summation of all the values in this particular column, which is 2,13,000. Now you may be asking me a question, will it take distinct values or all values? Here we did not give any keyword like distinct. So it takes all the values. In case user wants the summation of unique values only or distinct values only, in that case the query should be modified like this. Select sum, the aggregate function, we will be providing column name as the parameter. But before that, add distinct keyword so that we will be getting the summation for the distinct values. Here what column I have chosen? Age. So when you add the age values, 25 + 24 + 25 + 32, we'll be getting a number. So we will be getting 106 if we don't use distinct keyword. But if we use distinct keyword, we will be getting 81 only. The reason is, it takes 25, 24, it skips this 25, because we have mentioned it is distinct. So it skips this 25. So the answer here will be 25 + 24 + 32, which is exactly 81.
We are done with the second aggregate function sum. Let's move on to the third function, average. So the name itself says that it's going to return the average value of the selected columns. And this average function works on both numeric and non-null values only. Let's take the same example. Let's assume the user is applying a query, select average of age from student. This user wants to know the average of this particular column age. This is 25 + 24 + 25 + 32, divided by four, the number of values. So here we have four rows, so all values are taken, they are added and the average is taken. So the answer for this query will be 26. So obviously 26 is the average for all these four values. Let's assume if the user wants the average for distinct values. So in this case the user can simply change the query by inserting the distinct keyword before the column name. So in this case, the output is going to be varying. For this, the output is going to be 27. The reason is we have taken only distinct values. I mean 25, 24, 32 only. We are skipping this 25 because already we have taken this.
We are done with the third aggregate function average. Now let's move on to the fourth aggregate function minimum. So from the name itself we can easily witness that this is going to return the lowest value or the minimum value in the selected columns. And this minimum function will work on non-null values only. Let's take the same example. In this example, let's assume the user is applying a query, select minimum in the salary column from student table. So we are referring this table student and this particular column salary. So what the user is expecting? The minimum value from this column. So that is why he has given min, which is for minimum from the column salary. So the output is going to be obviously 35,000 because 35,000 is the minimum value in that particular column. And coming to the last aggregate function max, this is going to return the highest value in the selected columns, which means the biggest number or the maximum value. Like minimum, this is also going to work on non-null values only. I'm bringing in the same example. Let's assume the user is giving the query, select max, the column name salary from student. What do we mean by this? The user is intended to get the maximum value in the salary column from the table student. So from this table student, there is a column called salary. And what's the maximum value here? 68,000. And user wants to know the maximum value from this column.
So aggregate functions are really helpful when we deal with Group By and Having clauses. At the same time, when we have a database which contains only 100 or 200 rows, it's easy to manually go and search. But think about an organization which contains thousands and thousands of data. In that case, we cannot go and manually search for the minimum value or the maximum value or doing summation or finding the average or counting the rows. In those cases, these aggregate functions are the savior from spending a lot of time and effort. So what we have learned in this presentation? We have learned about the various aggregate functions count, sum, average, minimum, and maximum. In the next presentation, we will deal much more powerful things that we can do with these aggregate functions with the combination of Group By and Having clause. I hope you guys enjoyed this presentation and thank you for watching.
