How to use Aggregate Functions in SubQuery with Database

February 13, 2014 , , , 0 Comments

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.

How to use Aggregate Functions in SubQuery with Database

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.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google