CODEWITHSUNDEEP
sqldatabaseoracle-database
PKG
PKG

Reputation: 301

Difference from sum of column separately from different tables having same columns in sql

I have 2 tables with same columns TABLE A and TABLE B with columns QUANTITY and AMOUNT.

For single column difference i used this - 

SELECT ( 
(SELECT SUM(QUANTITY) FROM TABLE A ) -  (SELECT SUM(QUANTITY) FROM TABLE B )
) 
AS DIFF FROM DUAL;

I want the difference of QUANTITY and AMOUNT columns.

SELECT SUM(QUANTITY) AS SUM1, SUM(AMOUNT) SUM2 FROM TABLE A;

SELECT SUM(QUANTITY) AS SUM3, SUM(AMOUNT) SUM4 FROM TABLE B;

I want to achieve-

SELECT (SUM1 - SUM3) AS DIFF1, (SUM2 - SUM4) AS DIFF2;

Can i do it in single select query or should i go for function or something else??

Thanks for any help in advance.

Upvotes: 0

Views: 91

Answers (2)

wvdz
wvdz

Reputation: 16651

It can be simply extended to

SELECT 
(SELECT SUM(QUANTITY) FROM A ) -  (SELECT SUM(QUANTITY) FROM B )
AS DIFF_SUM,
(SELECT SUM(AMOUNT) FROM A ) -  (SELECT SUM(AMOUNT) FROM B )
AS DIFF_QUANT FROM DUAL;

Another, perhaps more readable way to do this:

WITH a1 AS
(SELECT SUM(QUANTITY) AS SUM1, SUM(AMOUNT) SUM2 FROM A),
b1 AS
(SELECT SUM(QUANTITY) AS SUM3, SUM(AMOUNT) SUM4 FROM B)
SELECT (SUM1 - SUM3) AS DIFF1, (SUM2 - SUM4) AS DIFF2 FROM a1, b1;

If you want to really combine them, here's a third way:

SELECT SUM(Quantity), SUM(Amount)
FROM
(SELECT Quantity, Amount FROM A
UNION ALL
SELECT -Quantity, -Amount FROM B)

Upvotes: 1

Anthony Horne
Anthony Horne

Reputation: 2522

OR: 

SELECT 
SUM(A.AMOUNT) AS SUMAMOUNTA
,SUM(A.QUANTITY) AS SUMQTYA
,SUM(B.AMOUNT) AS SUMAMOUNTB
,SUM(B.QUANTITY) AS SUMQTYB
,SUM(A.QUANTITY) - SUM(B.QUANTITY) AS DIFFQTY
,SUM(A.AMOUNT) - SUM(B.AMOUNT) DIFFSUM
FROM 
TABLEA A, TABLEB B;

Upvotes: 0

Related Questions