Home » RDBMS Server » Server Administration » Cumulative Salary
Cumulative Salary [message #370578] Tue, 28 December 1999 03:29 Go to next message
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 #370584 is a reply to message #370578] Mon, 03 January 2000 09:04 Go to previous messageGo to next message
Van der Auwera Thierry
Messages: 1
Registered: January 2000
Junior Member
I not understand the problem, maybe it is as simple as the following sql???

SELECT SUM(sal)
FROM emp
WHERE ...
<GROUP DEPTNO BY> (???)
Re: Cumulative Salary [message #370585 is a reply to message #370578] Mon, 03 January 2000 12:08 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: dbms_output.put_line
Next Topic: Blank space insertion into a Database field of Var
Goto Forum:
  


Current Time: Fri Oct 30 03:33:18 CDT 2020