| Cumulative Salary [message #370578] |
Tue, 28 December 1999 03:29  |
beesetty
Messages: 3
Registered: December 1999
Location: Singapore
|
Junior Member |
|
|
I have emp table Structure is
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
i want to get a cumulative salary with sql without using plsql. can any one help me
Thank U.
|
|
|
|
|
|
| Re: Cumulative Salary [message #370585 is a reply to message #370578] |
Mon, 03 January 2000 12:08  |
hmg
Messages: 40
Registered: March 1999
|
Member |
|
|
This solution is based on a solution by Luis Claudio some messages ago.
select b.empno, b.ename, b.sal, sum(a.sal) from emp a,
(select empno, ename, sal from emp group by empno, ename, sal) b
where a.empno <= B.EMPNO
group by b.empno, b.ename, b.sal;
|
|
|
|
| Re: Cumulative Salary [message #370589 is a reply to message #370584] |
Mon, 03 January 2000 21:22 |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Murali,
It depends on what you mean by cumulative salary, if you mean the sum of all the salaries in the table you can:
SELECT SUM(sal) FROM emp;
If you mean the sum of all salaries, plus all commissions then you can:
SELECT SUM(sal) + SUM(NVL(comm,0)) FROM emp;
If you have multiple records for a given employee and mean the sum of all salary paid to each individual employee you can:
SELECT empno, SUM(sal) FROM emp GROUP BY empno;
If you want the salary + commission for each employee and have 1 record per employee, you can:
SELECT empno, sal + NVL(comm,0) FROM emp;
If you want the above and have multiple records per employee, you can
SELECT empno, SUM(sal + NVL(comm,0)) FROM emp GROUP BY empno;
Hope this helps,
Paul
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
