Analytic functions and means of window clause for calculating sum-CodePudding

I'm using Oracle and SQL Developer. I have downloaded HR schema and need to do some queries with it. Now I'm working with table Employees. As a user, I need the sum of salary of 3 employees with highest salary in each department. I have done query for defining 3 employees with highest salary in each department:

SELECT
    *
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    )
WHERE
    result <= 3;

I need to use means of window clause. I have done something like this:

SELECT
    department_id,
    SUM(salary)
    OVER (PARTITION BY department_id ORDER BY salary       
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) total_sal
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    )
WHERE
    result <= 3;

Here is the result:

It has the necessary sum for 3 people in department and other unnnecessary results for 2 and so on. I need such result:

How can I modify my query to receive appropriate result (I need to use a window clause and analytic fuctions)?

CodePudding user response：

You want aggregation rather than windowing in the outer query:

SELECT
    department_id,
    SUM(salary) total_sal
FROM
    (
        SELECT
            employee_id,
            first_name
            || ' '
            || last_name,
            department_id,
            salary,
            ROW_NUMBER()
            OVER(PARTITION BY department_id
                 ORDER BY
                     salary DESC  
                 --ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING     
            ) result
        FROM
            employees
    ) e
WHERE
    result <= 3
GROUP BY department_id

I we were to do the same task with window functions only, then, starting from the existing query, we can either add another level of nesting of some sort, or use WITH TIES. Both pursue the same effect, which is to limit the results to one row per group.

The latter would look like:

SELECT 
    department_id,
    SUM(salary) OVER(PARTITION BY department_id) total_sal
FROM (
        SELECT e.*, 
            ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) result
        FROM employees e
) e
WHERE result <= 3
ORDER BY result FETCH FIRST ROW WITH TIES

While the former would phrase as:

SELECT department_id, total_sal
FROM (
    SELECT e.*,
        SUM(salary) OVER(PARTITION BY department_id) total_sal
    FROM (
        SELECT e.*, 
            ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) result
        FROM employees e
    ) e
    WHERE result <= 3
) e
where result = 1