question

You have two options. One is to repeat the code for TP and Total and subtract the first expression from the later. The second option is to use a Common Table Expression An example: create table AliasTest(a int, b int, c int); insert AliasTest(a,b,c) values(1,2,3); WITH CTE AS( SELECT a*b as ATIMESB, a+c as APLUSC FROM AliasTest ) SELECT ATIMESB, APLUSC, ATIMESB - APLUSC as Difference from cte;

There's no way to do that directly without rerunning the calculations. So, a few options: 1. Rerun the calculations to build the column you want 2. Use a CTE - [Common Table Expression][1] - defined as per your query, and select the values from it with the extra column (probably the simplest to write) 3. Create a VIEW based on your query, and SELECT from that 4. Store the data from your SELECT statement into a temporary table (or a table variable), and use that. 5. Do the final calculation at the presentation layer - your application, report, whatever. **Edit** So, the CTE option: ; WITH MyCTE AS ( SELECT description , C.date , SUM(C.quantity) AS quantity , ( C.quantity ) + ( SELECT COALESCE(SUM(item_count.quantity), 0) FROM item_count JOIN supplier ON supplier.supplier_id = item_count.supplier_id WHERE sup_name = '4PS' AND MONTH(item_count.date) < MONTH(now()) ) AS total , SUM(D.quantity) AS TP FROM item AS A JOIN supplier AS B ON B.supplier_id = A.supplier_id JOIN item_count AS C ON C.item_id = A.item_id JOIN [transaction] AS D ON D.item_id = A.item_id WHERE sup_name = '4PS' AND MONTH(C.date) = MONTH(now()) AND MONTH(d.trans_date) = MONTH(now()) AND company = '4PS' ) SELECT Description , Date , Quantity , Total , TP , Total - TP AS NewTotal FROM MyCTE ORDER BY Description Note that you can't have an ORDER BY clause in a CTE - after all, you don't have one in a TABLE, or in a VIEW, right? Second **EDIT** - given you now say you're using MySQL rather than SQL Server, CTE's aren't available. I would suggest a further subquery, along these lines: SELECT Description , Date , Quantity , Total , TP , Total - TP AS NewTotal FROM ( SELECT description , C.date , SUM(C.quantity) AS quantity , ( C.quantity ) + ( SELECT COALESCE(SUM(item_count.quantity), 0) FROM item_count JOIN supplier ON supplier.supplier_id = item_count.supplier_id WHERE sup_name = '4PS' AND MONTH(item_count.date) < MONTH(now()) ) AS total , SUM(D.quantity) AS TP FROM item AS A JOIN supplier AS B ON B.supplier_id = A.supplier_id JOIN item_count AS C ON C.item_id = A.item_id JOIN [transaction] AS D ON D.item_id = A.item_id WHERE sup_name = '4PS' AND MONTH(C.date) = MONTH(now()) AND MONTH(d.trans_date) = MONTH(now()) AND company = '4PS' ) ORDER BY Description [1]: http://msdn.microsoft.com/en-us/library/ms175972.aspx

oh... It makes sense but I can't run it on wamp. is CTE on SQL? sorry noob ^_^