MySQL - want minus two SUM () values from two SELECT statements
I want to be able to grab the two values I created in SUM () from two SELECT queries and minus those values to get the result (OutstandingFunds).
These are my two SELECT queries:
Statement (1):
SELECT SUM(Cf.Amount) AS ClearedFunds
FROM (
SELECT Amount FROM PAYMENT1 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT2 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT3 WHERE `Status` = "Cleared") AS Cf;
Statement (2):
SELECT SUM(Price) AS TotalSales
FROM PROPERTY
WHERE Status = "Sold";
thank you for your time
source to share
If you don't need to show a separate subtotal for total sales and cleared funds, you can do it like this:
SELECT SUM(Total.`Price`) AS ClearedFunds
FROM (
SELECT `Price` FROM PROPERTY WHERE `Status` = 'Sold'
UNION ALL
SELECT (`Amount` * -1) AS `Price` FROM PAYMENT1 WHERE `Status` = 'Cleared'
UNION ALL
SELECT (`Amount` * -1) AS `Price` FROM PAYMENT2 WHERE `Status` = 'Cleared'
UNION ALL
SELECT (`Amount` * -1) AS `Price` FROM PAYMENT3 WHERE `Status` = 'Cleared'
) AS Total;
I am assuming you want to deduct cleared funds from total sales here.
source to share
You were almost there ... works here SQL
:
SELECT (SELECT SUM(Cf.Amount) AS ClearedFunds
FROM (
SELECT Amount FROM PAYMENT1 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT2 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT3 WHERE `Status` = "Cleared") as Cf)
- (SELECT SUM(Price) AS TotalSales
FROM PROPERTY
WHERE Status = "Sold") as Result;
Here's a SQL Fiddle so you can play with the test data: http://sqlfiddle.com/#!2/18677/11
source to share
You can use SELECT ... INTO ...
, in your case:
SELECT SUM(Cf.Amount) AS ClearedFunds
FROM (
SELECT Amount FROM PAYMENT1 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT2 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT3 WHERE `Status` = "Cleared") INTO @cf;
SELECT SUM(Price) AS TotalSales
FROM PROPERTY
WHERE Status = "Sold" INTO @ts;
Then you can subtract or use them in any other request, for example:
SELECT @ts - @cf;
source to share
Are you looking for this?
SELECT C.ClearedFunds - P.TotalSales
FROM (
SELECT SUM(Cf.Amount) AS ClearedFunds
FROM (
SELECT Amount FROM PAYMENT1 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT2 WHERE `Status` = "Cleared"
UNION ALL
SELECT Amount FROM PAYMENT3 WHERE `Status` = "Cleared") AS Cf) C,
(SELECT SUM(Price) AS TotalSales
FROM PROPERTY
WHERE Status = "Sold") P
;
source to share