adding a calculated/virtual column to an existing query

Question

My query returns a sales column total for each month and a purchases total for each month, for certain categories.

SELECT theMonth, 
       sum(Sales) as sumSales, 
       sum(Saleswotax) as sumSaleswotax, 
       sum(Purchases) as sumPurchases, 
       sum(Purchaseswotax) as sumPurchaseswotax 
 FROM ( SELECT date_format(saledate, '%Y-%m') AS theMonth, 
               sales.cost as Sales, 
               ROUND(sales.cost*0.85, 2) AS Saleswotax, 
               0 AS Purchases, 
               0 AS Purchaseswotax 
          FROM sales, products 
         WHERE sales.product = products.name 
           AND category='Food'
        UNION ALL
        SELECT date_format(purchasedate, '%Y-%m') AS theMonth, 
               0 as Sales, 
               0 AS Saleswotax, 
               purchases.cost as Purchases, 
               ROUND(purchases.cost*0.85, 2) AS Purchaseswotax, 
          FROM purchases) AS all_costs
    group by theMonth

I am trying to return a column(that does not actually exist in the table) in my query that is just a calculation of an existing table., ie the saleswotax and purchaseswotax columns.

I am using a function, and returning it AS a name...why is it not working?

Answer 1

In the union, you used 0 as sales and purchases columns, but didn't also do that for -wotax columns. They need to match up for the union to work properly (I think you know that, since you did it for Sales and Purchases).

Answer 2

Last time when I saw, there was no declarative support for computed fields in MySQL. You would have to either add computed columns to your table and fill them using an UPDATE/INSERT trigger. Or create Views with additional computed columns.

Answer 3

You need to remove the comma after AS Purchasewotax in the latter half of the UNION:

SELECT theMonth, 
       sum(Sales) as sumSales, 
       sum(Saleswotax) as sumSaleswotax, 
       sum(Purchases) as sumPurchases, 
       sum(Purchaseswotax) as sumPurchaseswotax 
 FROM ( SELECT date_format(saledate, '%Y-%m') AS theMonth, 
               sales.cost as Sales, 
               ROUND(sales.cost*0.85, 2) AS Saleswotax, 
               0 AS Purchases, 
               0 AS Purchaseswotax 
          FROM sales, products 
         WHERE sales.product = products.name 
           AND category='Food'
        UNION ALL
        SELECT date_format(purchasedate, '%Y-%m') AS theMonth, 
               0 as Sales, 
               0 AS Saleswotax, 
               purchases.cost as Purchases, 
               ROUND(purchases.cost*0.85, 2) AS Purchaseswotax
          FROM purchases) AS all_costs
 GROUP BY theMonth