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>