MySQL Forums
Forum List  »  InnoDB

Using UNION to add selected field in multiple select statements?
Posted by: Don Ireland
Date: December 29, 2015 09:25PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Using UNION to add selected field in multiple select statements?
1733
December 29, 2015 09:25PM


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.