Stewart Dillard Stewart Dillard - 7 months ago 8
SQL Question

How do I subtract two declared variables in MYSQL

The question I am working on is as follows:

What is the difference in the amount received for each month of 2004 compared to 2003?

This is what I have so far,

SELECT @2003 = (SELECT sum(amount) FROM Payments, Orders
WHERE YEAR(orderDate) = 2003
AND Payments.customerNumber = Orders.customerNumber
GROUP BY MONTH(orderDate));

SELECT @2004 = (SELECT sum(amount) FROM Payments, Orders
WHERE YEAR(orderDate) = 2004
AND Payments.customerNumber = Orders.customerNumber
GROUP BY MONTH(orderDate));

SELECT MONTH(orderDate), (@2004 - @2003) AS Diff
FROM Payments, Orders
WHERE Orders.customerNumber = Payments.customerNumber
Group By MONTH(orderDate);


In the output I am getting the months but for Diff I am getting NULL please help. Thanks

Answer

I cannot test this because I don't have your tables, but try something like this:

SELECT a.orderMonth, (a.orderTotal - b.orderTotal ) AS Diff 
FROM 
    (SELECT MONTH(orderDate) as orderMonth,sum(amount) as orderTotal 
    FROM Payments, Orders
    WHERE YEAR(orderDate) = 2004
    AND Payments.customerNumber = Orders.customerNumber
        GROUP BY MONTH(orderDate)) as a,
   (SELECT MONTH(orderDate) as orderMonth,sum(amount) as orderTotal FROM Payments, Orders
    WHERE YEAR(orderDate) = 2003
    AND Payments.customerNumber = Orders.customerNumber
        GROUP BY MONTH(orderDate)) as b
WHERE a.orderMonth=b.orderMonth