While using subqueries, you can also use aggregate functions in the subqueries to generate aggregated values from the inner query. For example, in a manufacturing organization, the management wants to view the sale records of all the items whose sale records are higher than the average sale record of a particular product.
Therefore, the user first needs to obtain the average of a particular product and then find all the records whose sale record exceeds the average value. For this, you can use aggregate functions inside the subquery.
The following example displays the BusinessEntityID of those employees whose vacation hours are greater ta e average vacation hours of employees with title as ‘Marketing Assistant’:
SELECT BusinessEntityID FROM HumanResources.Employee
WHERE VacationHours > (SELECT AVG(VacationHours) FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Assistant')
The output of the subquery that uses the aggregate function is shown in the following figure.
In the preceding example, the inner query returns the average vacation hours of all the employees who are titled as Marketing Assistant. The outer query uses the comparison operator ‘>’ to retrieve the employee ID of all those employees who have vacation hours more than the average vacation hours assigned for a Marketing Assistant.
Therefore, the user first needs to obtain the average of a particular product and then find all the records whose sale record exceeds the average value. For this, you can use aggregate functions inside the subquery.
The following example displays the BusinessEntityID of those employees whose vacation hours are greater ta e average vacation hours of employees with title as ‘Marketing Assistant’:
SELECT BusinessEntityID FROM HumanResources.Employee
WHERE VacationHours > (SELECT AVG(VacationHours) FROM HumanResources.Employee
WHERE JobTitle = 'Marketing Assistant')
The output of the subquery that uses the aggregate function is shown in the following figure.
In the preceding example, the inner query returns the average vacation hours of all the employees who are titled as Marketing Assistant. The outer query uses the comparison operator ‘>’ to retrieve the employee ID of all those employees who have vacation hours more than the average vacation hours assigned for a Marketing Assistant.