w3resource

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.


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

Previous PostgreSQL Exercise: Function to Calculate Factorial of a Number.
Next PostgreSQL Exercise: Function to Reverse a String.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.