Get Employee Count by Department using PostgreSQL Function

Function to Count Employees in a Department

Write a PostgreSQL function that returns the count of employees in a specific department.

Solution:

-- Create a function named count_employees that takes a department ID as input and returns an integer
CREATE FUNCTION count_employees(dept_id INT) RETURNS INT AS $$
-- Declare a variable to store the employee count
DECLARE
emp_count INT;
BEGIN
-- Count the number of employees in the specified department and store it in emp_count
SELECT COUNT(*) INTO emp_count FROM Employees WHERE department_id = dept_id;
-- Return the employee count
RETURN emp_count;
END;
-- Specify the language used in the function as PL/pgSQL
$$ LANGUAGE plpgsql;

Explanation:

• Purpose of the Query:
• Counts the number of employees in a department.
• Real-World Application:
• Used in HR systems to analyze workforce distribution.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL function to find the department with the most employees.
• Write a PostgreSQL function that returns the number of employees who have been working for more than 10 years.
• Write a PostgreSQL function that counts employees in a department but only if the department has more than five employees.
• Write a PostgreSQL function that returns the number of employees in a department, excluding interns.

Go to:

• Comprehensive Guide to writing PL/pgSQL Functions in PostgreSQL Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Function to Calculate Factorial of a Number.
NEXT : Function to Reverse a String.

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