Aggregating Departmental Salaries with Non-Recursive CTE

Create a Non-Recursive Common Table Expression (CTE)

Write a SQL query to calculate the total salary for each department using a non-recursive CTE.

Solution:

-- Calculate total salary per department using a non-recursive CTE.
WITH DepartmentSalaryCTE AS (
    SELECT 
        DepartmentID,
        SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT * FROM DepartmentSalaryCTE;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use a non-recursive CTE to calculate the total salary for each department.

2. Key Components :

1. WITH DepartmentSalaryCTE : Defines the CTE.
2. SUM(Salary) : Aggregates salaries within each department.
3. GROUP BY DepartmentID : Groups data by department.

3. Why use Non-Recursive CTEs? :

1. CTEs improve query readability and modularity by breaking down complex queries into logical steps.

4. Real-World Application :

1. For example, in financial systems, you might use this query to summarize departmental expenses.

Additional Notes:

• Non-recursive CTEs are ideal for simplifying complex queries without recursion.
• Use this exercise to teach how to modularize queries for better maintainability.

For more Practice: Solve these Related Problems:

• Write a SQL query to calculate the average salary per department using a non-recursive CTE.
• Write a SQL query to compute the total bonus paid to employees in each team using a non-recursive CTE.
• Write a SQL query to find the highest-paid employee in each department using a non-recursive CTE.
• Write a SQL query to calculate the total project hours logged by employees in each division using a non-recursive CTE.

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

Previous SQL Exercise: Use NTILE() for Data Bucketing.
Next SQL Exercise: Handle XML Data with XPath Queries.