Search code examples
sqlsumaliassql-server-2016-express

SUM of 2 aliases in SQL Server


The Balance column shows current Stock on Hand + Open PO minus QTY Entered.

I have an existing query with ALIASes in the SELECT statement:

SELECT dbo.Items.ItemNo, 
       dbo.Items.Name, 
       dbo.Vendors.Name AS VendorName, 
       ISNULL(ItemsStock.StockOnHand, 0) AS [Stock on Hand], 
       ISNULL(PO.[Open PO's'], 0) AS [Open PO], 
       ISNULL(WO.OpenWOQTY, 0) AS [QTY Entered].

How to add an additional column called Balance to sum Stock on Hand and Open PO then deduct QTY Entered?


Solution

  • SELECT dbo.Items.ItemNo, 
           dbo.Items.Name, 
           dbo.Vendors.Name AS VendorName, 
           ISNULL(ItemsStock.StockOnHand, 0) AS [Stock on Hand], 
           ISNULL(PO.[Open PO's'], 0) AS [Open PO], 
           ISNULL(WO.OpenWOQTY, 0) AS [QTY Entered],
           ISNULL(ItemsStock.StockOnHand, 0) + ISNULL(PO.[Open PO's'], 0) - ISNULL(WO.OpenWOQTY, 0) AS [Balance]
    FROM table