SELECT sql to return sum of values in columns of 3 rows as second column value and so on

Question

Consider this

select first_name, salary
from employees;
FIRST_NAME               SALARY
-------------------- ----------
Jack                       8400
Kimberely                  7000
Charles                    6200
Winston                    3200
Jean                       3100
Martha                     2500
Girard                     2800
Nandita                    4200

I need sum of 3 salaries as SUM column value.

Expected Result:

FIRST_NAME               SALARY       SUM
-------------------- ---------- ---------
Jack                       8400     21600
Kimberely                  7000     21600
Charles                    6200     21600
Winston                    3200      8800
Jean                       3100      8800
Martha                     2500      8800
Girard                     2800      7000
Nandita                    4200      7000

This is the SUM column contains the sum of salaries of 3 rows and so on.. In the end if 1 row or just 2 rows remain, then the SUM column should contain the sum of those 1 or 2 values itself as shown.

Answer 1

Here are a couple of ways of doing this:

with employees as (select 'Jack' first_name, 8700 salary from dual union all
                   select 'Kimberely' first_name, 7000 salary from dual union all
                   select 'Charles' first_name, 6200 salary from dual union all
                   select 'Winston' first_name, 3200 salary from dual union all
                   select 'Jean' first_name, 3100 salary from dual union all
                   select 'Martha' first_name, 2500 salary from dual union all
                   select 'Girard' first_name, 2800 salary from dual union all
                   select 'Nandita' first_name, 4200 salary from dual)
-- end of setting up a subquery mimicking your employees table; see SQL below:
select first_name,
       salary,
       sum(salary) over (partition by grp1) sum_salary_grps1,
       sum(salary) over (partition by grp2) sum_salary_grps2
from   (select first_name,
               salary,
               ceil((row_number() over (order by salary desc))/3) grp1,
               ceil(rownum/3) grp2
        from   employees);


FIRST_NAME     SALARY SUM_SALARY_GRPS1 SUM_SALARY_GRPS2
---------- ---------- ---------------- ----------------
Jack             8700            21900            21900
Kimberely        7000            21900            21900
Charles          6200            21900            21900
Nandita          4200            10500             7000
Winston          3200            10500             8800
Jean             3100            10500             8800
Girard           2800             5300             7000
Martha           2500             5300             8800

You'll note that I ordered the rows by salary descending when using the row_number() analytic function to generate the group number, whereas the rownum method of getting the group number has no inherent ordering.

For preference, I would go with row_number() to generate the group numbers, but YMMV.

Answer 2

Rows are not in any order in a heap-organized table, which is a standard Oracle table. Therefore when you say that you want to group 3 rows, the bigger question is which 3 rows, in what order?

You need to make sure you have an attribute to group the rows, without that any 3 random rows could be picked.

Let's see an example from standard SCOTT.EMP table. I would calculate the SUM for each DEPARTMENT.

SQL> SELECT ename, SUM(sal) over(partition BY deptno) sum_sal FROM emp;

ENAME         SUM_SAL
---------- ----------
CLARK            8750
KING             8750
MILLER           8750
JONES           10875
FORD            10875
ADAMS           10875
SMITH           10875
SCOTT           10875
WARD             9400
TURNER           9400
ALLEN            9400
JAMES            9400
BLAKE            9400
MARTIN           9400

14 rows selected.