Creating a Scalar Function with Aggregation

Create a Scalar Function with Aggregation

Write a SQL query to create a scalar function that calculates the average salary of employees in a department.

Solution:

-- Create a scalar function with aggregation.
CREATE FUNCTION GetAverageSalaryByDepartment
    (@DepartmentID INT) -- Input parameter for the department ID.
RETURNS DECIMAL(10, 2) -- Return type.
AS
BEGIN
    DECLARE @AverageSalary DECIMAL(10, 2);

    -- Calculate the average salary for the given department.
    SELECT @AverageSalary = AVG(Salary)
    FROM Employees
    WHERE DepartmentID = @DepartmentID;

    RETURN ISNULL(@AverageSalary, 0); -- Return 0 if no employees exist in the department.
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a scalar function that calculates the average salary of employees in a specific department.

2. Key Components :

1. CREATE FUNCTION : Defines the scalar function.
2. AVG : Aggregates the salary values for the specified department.
3. ISNULL : Handles cases where no employees exist in the department.

3. Why Use Aggregation in Functions? :

1. Functions with aggregation simplify complex calculations and make them reusable.

4. Real-World Application :

1. For example, in HR systems, you might use this function to analyze salary trends across departments.

Additional Notes:

• Scalar functions with aggregation are useful for summarizing data but may impact performance in large queries.
• Use this exercise to teach how to handle edge cases like empty result sets.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a scalar function that calculates the median salary of employees in a department.
• Write a SQL query to create a scalar function that computes the standard deviation of a numeric column in a table.
• Write a SQL query to create a scalar function that aggregates data to calculate the total revenue generated by a specific product category.
• Write a SQL query to create a scalar function that finds the maximum value among a set of calculated expressions.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Create a Trigger for Audit Logging.
Next SQL Exercise: Create a Stored Procedure with Temporary Tables.