Solved

Refer SQL column alias for calculation in other column

Posted on 2008-10-24
11
1,487 Views
Last Modified: 2011-10-19
Please refer my following query. In Line 3, I want to use:

B.Quantity * Item_MRP

Please note that Item_MRP is an alias name for a query in Line 2. How to use this way?

I also want to know how I can improve the performance of the following query.
SELECT C.CATEGORYNAME, A.ITEMNAME, B.QUANTITY,
        (SELECT MRP FROM STOCKENTRY WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID)) AS ITEM_MRP,
        B.QUANTITY * (SELECT MRP FROM STOCKENTRY 
        WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID))  AS VALUE
        FROM ITEMMASTER A, STOCKENTRY B, CATEGORYMASTER C
        WHERE A.ITEMID=B.ITEMID AND 
        C.CATEGORYID = (SELECT CATEGORYID FROM ITEMMASTER WHERE ITEMID = A.ITEMID) AND
        B.INVOICEDATE BETWEEN @DATE1 AND @DATE2
        GROUP BY C.CATEGORYNAME, B.INVOICEDATE, A.ITEMNAME

Open in new window

0
Comment
Question by:rpkhare
  • 5
  • 5
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22798805
You can't do that.  You could put the whole thing into a derived table like this.



select *,quantity*item_mrp from (
SELECT C.CATEGORYNAME, A.ITEMNAME, B.QUANTITY,
        (SELECT MRP FROM STOCKENTRY WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID)) AS ITEM_MRP,
        B.QUANTITY * (SELECT MRP FROM STOCKENTRY
        WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID))  AS VALUE
        FROM ITEMMASTER A, STOCKENTRY B, CATEGORYMASTER C
        WHERE A.ITEMID=B.ITEMID AND
        C.CATEGORYID = (SELECT CATEGORYID FROM ITEMMASTER WHERE ITEMID = A.ITEMID) AND
        B.INVOICEDATE BETWEEN @DATE1 AND @DATE2
        GROUP BY C.CATEGORYNAME, B.INVOICEDATE, A.ITEMNAME)a

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22804803
As Brandon has indicated cannot do it in T-SQL.  But perhaps you meant to only post in the MySQL zone and not in the MS SQL Server zone.  It may be possible in MySQL.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22807828
Is this mysql or mssql.  I have to argue that the solution is not only available but provided in http:#22798805.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 8

Author Comment

by:rpkhare
ID: 22807879
MySQL
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22808235
The same thing should work in mysql.  Have you tried what I posted?
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22810222
Brandon,

thanks for the concern. Attached is the image of the output columns. But I want in this way:

Category_Name, Item_Name, Quantity, Item_MRP, Value (Qty * Item_MRP)

Your query is returning Value and (Quantity * MRP), two redundant columns.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22810274
Attached image
Stock-Entry-Result.JPG
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22811820
That was merely an example of how to reference the computed column by name.  How am I to know what columns you want to display?  If you want less columns, change it from select * to the columns you want.
select Category_Name, Item_Name, Quantity, Item_MRP,Qty * Item_MRP as Value
 
 
from (
SELECT C.CATEGORYNAME, A.ITEMNAME, B.QUANTITY,
        (SELECT MRP FROM STOCKENTRY WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID)) AS ITEM_MRP,
        B.QUANTITY * (SELECT MRP FROM STOCKENTRY 
        WHERE ITEMID = A.ITEMID AND RECORDID = (SELECT MAX(RECORDID) FROM STOCKENTRY WHERE ITEMID = A.ITEMID))  AS VALUE
        FROM ITEMMASTER A, STOCKENTRY B, CATEGORYMASTER C
        WHERE A.ITEMID=B.ITEMID AND 
        C.CATEGORYID = (SELECT CATEGORYID FROM ITEMMASTER WHERE ITEMID = A.ITEMID) AND
        B.INVOICEDATE BETWEEN @DATE1 AND @DATE2
        GROUP BY C.CATEGORYNAME, B.INVOICEDATE, A.ITEMNAME)a

Open in new window

0
 
LVL 8

Author Comment

by:rpkhare
ID: 22812523
Thanks. One more thing I want to know is how to optimize the above query for performance.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22812553
That would require knowledge of indexes (and suggestions of) and restructuring the query.  I would keep this on topic for the PAQ which is about referencing a column by it's alias and open a new question for optimization.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22812559
Ok. Thanks. I am rating and closing this question.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question