查询以前的值相加

时间:2021-08-18 01:32:21

I have the table strucure as in the image查询以前的值相加.

我有图像中的表格结构。

I need to get the values added to the sum of previous values(shown in the REQUIRED RESULT) I tried with the following query

我需要将值添加到先前值的总和(显示在必需结果中)我尝试使用以下查询

SELECT empid,
sum(tot_hours) OVER (PARTITION BY empid ORDER BY empid ) AS tot_hours
        FROM empDet
       ORDER BY empid

But i get the following result set

但我得到以下结果集

查询以前的值相加

But I need the result as in the first picture.

但我需要第一张照片中的结果。

Can anyone help me doing this?

任何人都可以帮我这样做吗?

1 个解决方案

#1


4  

sum(tot_hours) OVER (PARTITION BY empid ORDER BY empid ) AS tot_hours

sum(tot_hours)OVER(由empid ORDER BY empid分配)AS tot_hours

Your ORDER BY is incorrect. If you want the running SUM on the TOT_HOURS, then you should order by tot_hours.

您的ORDER BY不正确。如果你想在TOT_HOURS上运行SUM,那么你应该按tot_hours排序。

For example, the below query will calculate the running sum of salary of employees in each department:

例如,以下查询将计算每个部门员工的工资总和:

SQL> SELECT deptno,
  2    sal,
  3    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS tot_sal
  4  FROM emp
  5  ORDER BY deptno;

    DEPTNO        SAL    TOT_SAL
---------- ---------- ----------
        10       1300       1300
        10       2450       3750
        10       5000       8750
        20        800        800
        20       1100       1900
        20       2975       4875
        20       3000      10875
        20       3000      10875
        30        950        950
        30       1250       3450
        30       1250       3450
        30       1500       4950
        30       1600       6550
        30       2850       9400

14 rows selected.

SQL>

Update For duplicate values, the running total would be duplicate. To make it unique, use UNBOUNDED PRECEDING clause. For example,

更新对于重复值,运行总计将重复。要使其唯一,请使用UNBOUNDED PRECEDING子句。例如,

SQL> SELECT empno, deptno,
  2    sal,
  3    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING) AS tot_sal
  4  FROM emp
  5  ORDER BY deptno;

     EMPNO     DEPTNO        SAL    TOT_SAL
---------- ---------- ---------- ----------
      7934         10       1300       1300
                   10       1300       2600
      7782         10       2450       5050
      7839         10       5000      10050
      7369         20        800        800
      7876         20       1100       1900
      7566         20       2975       4875
      7788         20       3000       7875
      7902         20       3000      10875
      7900         30        950        950
      7521         30       1250       2200
      7654         30       1250       3450
      7844         30       1500       4950
      7499         30       1600       6550
      7698         30       2850       9400

15 rows selected.

SQL>

#1


4  

sum(tot_hours) OVER (PARTITION BY empid ORDER BY empid ) AS tot_hours

sum(tot_hours)OVER(由empid ORDER BY empid分配)AS tot_hours

Your ORDER BY is incorrect. If you want the running SUM on the TOT_HOURS, then you should order by tot_hours.

您的ORDER BY不正确。如果你想在TOT_HOURS上运行SUM,那么你应该按tot_hours排序。

For example, the below query will calculate the running sum of salary of employees in each department:

例如,以下查询将计算每个部门员工的工资总和:

SQL> SELECT deptno,
  2    sal,
  3    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS tot_sal
  4  FROM emp
  5  ORDER BY deptno;

    DEPTNO        SAL    TOT_SAL
---------- ---------- ----------
        10       1300       1300
        10       2450       3750
        10       5000       8750
        20        800        800
        20       1100       1900
        20       2975       4875
        20       3000      10875
        20       3000      10875
        30        950        950
        30       1250       3450
        30       1250       3450
        30       1500       4950
        30       1600       6550
        30       2850       9400

14 rows selected.

SQL>

Update For duplicate values, the running total would be duplicate. To make it unique, use UNBOUNDED PRECEDING clause. For example,

更新对于重复值,运行总计将重复。要使其唯一,请使用UNBOUNDED PRECEDING子句。例如,

SQL> SELECT empno, deptno,
  2    sal,
  3    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS UNBOUNDED PRECEDING) AS tot_sal
  4  FROM emp
  5  ORDER BY deptno;

     EMPNO     DEPTNO        SAL    TOT_SAL
---------- ---------- ---------- ----------
      7934         10       1300       1300
                   10       1300       2600
      7782         10       2450       5050
      7839         10       5000      10050
      7369         20        800        800
      7876         20       1100       1900
      7566         20       2975       4875
      7788         20       3000       7875
      7902         20       3000      10875
      7900         30        950        950
      7521         30       1250       2200
      7654         30       1250       3450
      7844         30       1500       4950
      7499         30       1600       6550
      7698         30       2850       9400

15 rows selected.

SQL>