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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics