Steve Steve - 1 month ago 14
SQL Question

MySQL - Want to Minus two SUM() values from two SELECT statements

I want to be able to grab the two values that I have generated in SUM() from two SELECT queries and minus these values in order 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

Answer

If you don't need to show the 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 are wanting to subtract cleared funds from total sales here.

Comments