w3resource

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.

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.



Follow us on Facebook and Twitter for latest update.