Using UNION to add selected field in multiple select statements?
Hi all. I'm hoping that someone might help me with what I think needs to be a UNION statement. I've got 4 different SELECT statements that all return a DECIMAL amount. I need to add the first two sums and subtract the second two sums and return the answer. This answer will actually be a sub-query used in a INSERT statement.
# returns 9.15
SELECT sum( a.amount ) AS amount
FROM `transactions` AS p
INNER JOIN `transactions` AS a
INNER JOIN `parents` AS r ON ( r.parentID = p.id
AND r.childID = a.id )
WHERE p.userID = 4
AND p.acctID = 1;
# returns 5.50
SELECT sum( amount )AS aAmount FROM `transactions` WHERE userID = 4 AND acctID = 1;
# returns 5.50
SELECT sum( a.amount ) AS tsAmount
FROM `transactions` AS p
INNER JOIN `transactions` AS a
INNER JOIN `parents` AS r ON ( r.parentID = p.id
AND r.childID = a.id )
WHERE p.userID =4
AND a.catID =1
AND a.type = 'transfer';
# returns 5.50
SELECT sum( amount )AS tAmount FROM `transactions` WHERE userID = 4 AND catID = 1 AND type='transfer';
Here's my best attempt at using a UNION statement for this.
SELECT (aSplit.amount + a.amount - tSplit.amount - t.amount) from (
# returns 9.15
(SELECT sum( a.amount ) AS amount
FROM `transactions` AS p
INNER JOIN `transactions` AS a
INNER JOIN `parents` AS r ON ( r.parentID = p.id
AND r.childID = a.id )
WHERE p.userID = 4
AND p.acctID = 1) AS aSplit;
UNION DISTINCT
# returns 5.50
(SELECT sum( amount )AS aAmount FROM `transactions` WHERE userID = 4 AND acctID = 1) as a;
UNION DISTINCT
# returns 5.50
(SELECT sum( a.amount ) AS tsAmount
FROM `transactions` AS p
INNER JOIN `transactions` AS a
INNER JOIN `parents` AS r ON ( r.parentID = p.id
AND r.childID = a.id )
WHERE p.userID =4
AND a.catID =1
AND a.type = 'transfer') AS tSplit;
UNION DISTINCT
# returns 5.50
(SELECT sum( amount )AS tAmount FROM `transactions` WHERE userID = 4 AND catID = 1 AND type='transfer') AS t;
)
Don
Subject
Views
Written By
Posted
Using UNION to add selected field in multiple select statements?
1739
December 29, 2015 09:25PM
982
December 29, 2015 09:37PM
957
February 02, 2016 08:15AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.