Summarize Employee Data by Department in a View

Create a View with Aggregation

Write a PostgreSQL query to create a view that groups employees by department and shows the count of employees per department.

Solution:

-- Create a view to display the number of employees in each department.
CREATE VIEW DepartmentEmployeeCount AS
SELECT department, COUNT(*) AS employee_count
FROM Employees
GROUP BY department;

Explanation:

• Purpose of the Query:
• The goal is to aggregate data to provide summary information (employee counts by department).
• This demonstrates how to use GROUP BY within a view.
• Key Components:
• COUNT(*) AS employee_count : Aggregates the number of employees per group.
• GROUP BY department : Groups data by the department column.
• Real-World Application:
• Useful for management reporting and performance analysis.

Notes:

• Aggregated views provide dynamic summaries that reflect the current state of the underlying data.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to create a view that aggregates monthly sales totals from the Sales table, grouping the results by month.
• Write a PostgreSQL query to create a view that groups customer orders by status and calculates the average order value for each status.
• Write a PostgreSQL query to create a view that aggregates employee performance metrics by department and job role.
• Write a PostgreSQL query to create a view that computes total inventory value per product category using aggregation functions.

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

Previous PostgreSQL Exercise: Create a Filtered View.
Next PostgreSQL Exercise: Create a View with Computed Columns.