question

CrookS50 avatar image
CrookS50 asked

how to get the difference between two aliases

this is my query and its working. 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; I want to add a column which selects the difference of total and TP which are aliases. please help. tnx.
selectmysqlselectivity
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
On a side note, those calculations against your columns are going to absolutely kill performance: MONTH(C.Date), etc. I would suggest adding a column to the table that can store the calculated value and even index on it.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
The site name is SQL Server Central. Most people on here are SQL Server people. If you don't specify that you're using something other than SQL Server, you're likely to get answers for SQL Server, which may be problematic in a MySQL system.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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;
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
OK, @Magnus, you beat me to it. But at least I came up with a couple of other options, that may be even less efficient.
1 Like 1 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
And this teaches me to never say that there are a finite number of options to solve a problem :)
1 Like 1 ·
ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CrookS50 avatar image CrookS50 commented ·
where's the part to change... I'm planning to use CTE but I don't know where the part to use it though. a little help pls? ^_^.
0 Likes 0 ·
CrookS50 avatar image
CrookS50 answered
oh... It makes sense but I can't run it on wamp. is CTE on SQL? sorry noob ^_^
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Ah, right. MySQL. That does rule out the CTE option; however, you can convert it relatively straightforwardly into another sub-query. Hang on, I'll update my answer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.