1z0-071 Oracle Database SQL – AGGREGATE FUNCTIONS

1. AGGREGATE FUNCTIONS – GROUPS and SUBGROUPS

Functions or group functions operate on multiple rows per execution and bring a single row result. During the Introduction of Functions section we saw this a little bit and now let us get to the details. For example, if I do select some population from test one, it will scan all the rows of of the population column and will add values and bring the total value in a single row. So what happens is it works on a set of rows to aggregate those individual values based on the type of the operation and brings a single row a sum function. Just add the values. You may also notice that the sum function did one execution covering all the rows.

There are many such aggregate functions. For example, a count function will count the number of occurrences such as number of rows. In this case, it is also possible to have group functions to split the rows into subgroups and do calculations on each subgroup. For example, we can find the total budget of each team using this SQL with a group by class. Let’s go back a bit and understand the whole scenario. If I needed the total budget, I would have done just select some budget from office. But I need the total budget for the individual teams and not perhaps the whole company. So let me start with this sequel. Select Team sum budget from office.

Here I am selecting the sum of budget and we know that this will bring a single row result.But I am also selecting team column here with no aggregate function on it. So this should bring multiple rows. We have a problem here. One will fetch multiple rows and the other will fetch only one row, which is an aggregated result. What will happen if we run it just like this? Oracle will give an error. Now I modify the sequel here. I tell Oracle that I need the sum of the budget column, but not for the entire set of rows. I want you to group those rows into subgroups by the column team. Then add the rows of budget for those subgroups and bring me result for each group.

So here the rows will be grouped based on the values in the team column one group for marketing, one group for support. Now it will do the aggregate function on those subgroups. For example, sum of budget in marketing subgroup is 75,000, which is 50,000 plus 25,000. The sum of budget in support subgroup is 300,000, which is 200,000 plus 100,000. We can do other aggregate functions on these subgroups too. For example, max of budget will bring the greatest value of each subgroups. Average of budget will bring the average of those subgroups.

2. AGGREGATE FUNCTIONS – EXAMPLES

Select star from employees. Bring all the rows of the Employee table. Now from here let us determine what we want to group. If I need to find the total rows of this table, I can do select Count from Employees. This brings 10 seven rows. Now let me do a count Star of Commission Percentage, which is basically count Commission Percentage column from Employees. This brings me only 35. This doesn’t make sense. I know that the table has 107 rows, so each column should also have 107 rows. So then why this brings only 35 as the result? Because by default, count function ignores nulls in the total result.

Let us verify it by running this SQL where Commission percentage is not null. That if I rows and count of the null values 72. Let us add them both 10 seven, which is same as the total row count. Now let us count how many Job ID rows are in there? 10 seven. But how many unique job IDs. 19. What are those? And also give me the result in an order. Select distinct Job ID from Employees order by Job ID okay, how many employees are there in each Job ID position? Select Job ID count Star from Employees here you can notice this is an aggregate function and this is not an aggregate function. So you need to group by this. Okay? So select job ID count star from employees group by job ID. And I am ordering it by job ID.

Let me run this. It brings me the count of employees working on each Job IDs. Like we have two adbps, five accountants, like that. Okay, but I need to know how many people are there in SA rep and It prog select Job ID count Star from Employees where Job ID in Sare it prog group a Job ID and order by Job ID. Five people are there as It programmers and 30 people are there as Sales reps. Okay, my table is huge, so just give me an approximate count of Job IDs. Select approximate distinct Job ID from Employees this will give an approximate count if the table is huge. Since the employees table is not huge here, it brings the accurate result.

I need average salary, minimum salary, maximum salary, total salaries that are paid by the company. Okay? And this equal this is the average salary. This is the minimum salary, maximum salary and the total salary. Okay? Group the same thing by each Job ID. I’m selecting Job ID here, which is not an aggregate function. Here, everything else is an aggregate function. So I need to group by the one thing which is not aggregated, which is Job ID. Okay? So now we can say AC account. The average salary for those people. The people working in AC account is 8300, minimum salary is 8300. So like that we can provide details that’s how the group functions or the aggregate functions work. Thank you for watching.

3. FILTERING AGGREGATE FUNCTIONS – HAVING CLAUSE

Here I see different aggregates of the salaries of each job. IDs. Now, what if I want to see the same but just the rows where the average salary is above 10,000? Can I use where salary is greater than 10,000? No, this will restrict the rows at the source itself, this will ignore rows with values less than 10,000 and this will make all these aggregate functions to work on a smaller set of rows from each subgroups where the rows with salary less than 10,000 are not included and the result will be totally wrong. What we need is we need the aggregates to work with all the rows at the source.

Then we would like to do the filter on the result.I will repeat this again a where class filters data at the source itself. But what we need here is we need to get all the data at the source because we need the aggregate functions to work on a complete set. However, while bringing the result, we need to have a filter for that. We can use this having class. Remember a having class does the filter on the result set. Now this query will do the aggregates fully and bring just the rows where average salary is greater than 10,000. So this can be accomplished by having average salary greater than 10,000.