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.

Go to:

• PostgreSQL Views Exercises and Management Techniques Exercises Home. ↩
• PostgreSQL Exercises Home ↩

PREV : Create a Filtered View.
NEXT : Create a View with Computed Columns.

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