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:
- The goal is to demonstrate how to use a non-recursive CTE to calculate the total salary for each department.
- WITH DepartmentSalaryCTE : Defines the CTE.
- SUM(Salary) : Aggregates salaries within each department.
- GROUP BY DepartmentID : Groups data by department.
- CTEs improve query readability and modularity by breaking down complex queries into logical steps.
- For example, in financial systems, you might use this query to summarize departmental expenses.
1. Purpose of the Query :
2. Key Components :
3. Why use Non-Recursive CTEs? :
4. Real-World Application :
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.
Go to:
PREV : Use NTILE() for Data Bucketing.
NEXT : Handle XML Data with XPath Queries.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.