Sum two tables column in single SQL

Oracle Application Express is a rapid development tool for Web applications on the Oracle database.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Sum two tables column in single SQL

Post by admin »

CREATE TABLE ALLOW (ANO NUMBER(3), ANAME VARCHAR2(15),
CONSTRAINT ANO_PK PRIMARY KEY (ANO));

CREATE TABLE DED (DEDNO NUMBER(3), DEDNAME VARCHAR2(20),
CONSTRAINT DEDNO_PK PRIMARY KEY (DEDNO));

CREATE TABLE EMP(ENO NUMBER(5), ENAME VARCHAR2(20),
CONSTRAINT ENO_PK PRIMARY KEY(ENO));

CREATE TABLE EMPA(ENO NUMBER(5), ANO NUMBER(3), AMTA NUMBER(8,2),
CONSTRAINT ENO2_FK FOREIGN KEY(ENO) REFERENCES EMP(ENO),
CONSTRAINT ANO2_FK FOREIGN KEY(ANO) REFERENCES ALLOW(ANO));

CREATE TABLE EMPD(ENO NUMBER(5), DEDNO NUMBER(3), AMTD NUMBER(8,2),
CONSTRAINT ENO1_FK FOREIGN KEY(ENO) REFERENCES EMP(ENO),
CONSTRAINT DEDNO1_FK FOREIGN KEY(DEDNO) REFERENCES DED(DEDNO));

INSERT INTO ALLOW VALUES(1,'COLA');
INSERT INTO ALLOW VALUES(2,'TRANS');
INSERT INTO ALLOW VALUES(3,'MEAL');
INSERT INTO ALLOW VALUES(4,'B.SAL');

INSERT INTO DED VALUES(1,'STAMP');
INSERT INTO DED VALUES(2,'TAX');
INSERT INTO DED VALUES(3,'ABSENT');

INSERT INTO EMP VALUES(1,'AHMED');
INSERT INTO EMP VALUES(2,'SAEED');
INSERT INTO EMP VALUES(3,'HASAN');
INSERT INTO EMP VALUES(4,'SIKANDAR');

INSERT INTO EMPA VALUES(1,1,2500);
INSERT INTO EMPA VALUES(1,2,3000);
INSERT INTO EMPA VALUES(1,3,4200);
INSERT INTO EMPA VALUES(2,1,6300);
INSERT INTO EMPA VALUES(2,2,5000);
INSERT INTO EMPA VALUES(2,3,3600);
INSERT INTO EMPA VALUES(3,3,4800);
INSERT INTO EMPA VALUES(1,4,2650);
INSERT INTO EMPA VALUES(2,4,3000);
INSERT INTO EMPA VALUES(3,4,3300);

INSERT INTO EMPD VALUES(1,1,1000);
INSERT INTO EMPD VALUES(1,2,200);
INSERT INTO EMPD VALUES(1,3,500);
INSERT INTO EMPD VALUES(2,1,200);
INSERT INTO EMPD VALUES(2,2,440);
INSERT INTO EMPD VALUES(2,3,600);
INSERT INTO EMPD VALUES(3,1,440);
INSERT INTO EMPD VALUES(3,2,700);
INSERT INTO EMPD VALUES(3,3,920);

This SQL is giving wrong calculations

SQL> SELECT E.ENAME, SUM(TA.AMTA) AS "TOTAL ALLOW", SUM(TD.AMTD) AS "TOTAL DEDUCT"
FROM EMP E, EMPA TA, EMPD TD
WHERE E.ENO = TA.ENO AND E.ENO = TD.ENO
GROUP BY E.ENAME

ENAME SUM(TA.AMTA) SUM(TD.AMTD)
-------------------- ------------ -------------------------
SAEED 53700 4960
AHMED 37050 6800
HASAN 24300 4120

Sample Queries with correct result

-- This will return only matching records with EMP

select e.eno, e.ename, a.allowance, d.deduction from emp e,
(select eno, sum(amta) allowance from empa group by eno ) a,
(select eno,sum(amtd) deduction from EMPD group by eno ) d
where e.eno = a.eno and e.eno = d.eno
order by e.eno;

-- Following both will show all employees even if no entries in other tables

select eno, ename,
(select sum(amta) from empa a where e.eno = a.eno) Total_Allowance,
(select sum(amtd) from EMPD b where e.eno = b.eno) Total_deduction
from EMP e;

select e.eno, e.ename, nvl(a.allowance,0) Allowance, nvl(d.deduction,0) deduction from emp e,
(select eno, sum(amta) allowance from empa group by eno ) a,
(select eno,sum(amtd) deduction from EMPD group by eno ) d
where e.eno = a.eno(+) and e.eno = d.eno(+)
order by e.eno;

ENO ENAME ALLOWANCE DEDUCTION
1 AHMED 12350 1700
2 SAEED 17900 1240
3 HASAN 8100 2060
4 SIKANDAR 0 0
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests