Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1518
  • Last Modified:

Refer SQL column alias for calculation in other column

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
rpkhare
Asked:
rpkhare
  • 5
  • 5
1 Solution
 
BrandonGalderisiCommented:
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
 
Anthony PerkinsCommented:
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
 
BrandonGalderisiCommented:
Is this mysql or mssql.  I have to argue that the solution is not only available but provided in http:#22798805.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
rpkhareAuthor Commented:
MySQL
0
 
BrandonGalderisiCommented:
The same thing should work in mysql.  Have you tried what I posted?
0
 
rpkhareAuthor Commented:
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
 
rpkhareAuthor Commented:
Attached image
Stock-Entry-Result.JPG
0
 
BrandonGalderisiCommented:
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
 
rpkhareAuthor Commented:
Thanks. One more thing I want to know is how to optimize the above query for performance.
0
 
BrandonGalderisiCommented:
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
 
rpkhareAuthor Commented:
Ok. Thanks. I am rating and closing this question.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now