Formula for Computed Column Based on Different Table's Column

formula for computed column based on different table's column

You could create a user-defined function for this:

CREATE FUNCTION dbo.GetValue(@ncode INT, @recid INT)
RETURNS INT
AS 
   SELECT @recid * nvalue 
   FROM c_const 
   WHERE code = @ncode

and then use that to define your computed column:

ALTER TABLE dbo.YourTable
   ADD NewColumnName AS dbo.GetValue(ncodeValue, recIdValue)

SQL Server computed column select from another table

You won't be able to use columns from another table within a computed column expression. This is an extract from the MSDN documentation.

A computed column is computed from an expression that can use other columns in the same table.

You mentioned that your motivation for using a computed column was to increase performance. There are a lot of restrictions but an indexed view might add value here.

Computed column based on another computed column?

Computed columns cannot reference other computed columns. Though you ought to be able to just repeat the expression you would like to reference. From MSDN:

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

I should also add that if this were to work as you would hope, it would present all kinds of new issues you'd have to deal with. Presently, updates across many columns/rows occur in parallel and atomically.

Therefore, it wouldn't make sense to use a computed column in your calculation because it wouldn't exactly have a value...yet. If anything, you'd be using an old, un-updated value.

If you really wanted to avoid duplicating the expression, you could do this in a trigger, though I strongly urge you not do that. Triggers are no fun and should only be used by very savvy people in rare cases.

Create Computed Column using data from another table

You can create a user-defined function for that:

CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT Count(id)
         FROM   PictureUse
         WHERE  PictureId = @PictureId)
  END 

The computed column can then be added like this:

ALTER TABLE dbo.Picture
ADD NofUses AS dbo.CountUses(Id)

However, I would rather make a view for this:

CREATE VIEW PictureView
AS
  SELECT Picture.Id,
         PictureName,
         Picture.CreateDate,
         Count(PictureUse.Id) NofUses
  FROM   Picture
         JOIN PictureUse
           ON Picture.Id = PictureUse.PictureId
  GROUP  BY Picture.Id,
            PictureName,
            Picture.CreateDate 

How to use a calculated column to calculate another column in the same query using a subquery

You can use Temp_tables, Derived tables or Common table expressions (CTE) to obtain the result. Simple approach would be Derived table as you dont need much more coding.

  SELECT A.*
  ,  A.NLength/(A.NSpeedLimit * 88)  as [TravelTime]
  FROM
  (


  SELECT          
    sc.OBJECTID, 
    sn.Name, 
    case    when hn.side = 'Right Side' then ''
        else sc.LCity
    end as LCity, 
    case    when hn.side = 'Left Side' then ''
        else sc.RCity
    end as RCity, 
    case    when hn.side = 'Right Side' then ''
        else sc.LZip
    end as LZip, 
    case    when hn.side = 'Left Side' then ''
        else sc.RZip
    end as RZip, 
    sc.SHAPE.STLength() AS NLength,
    ISNULL(sc.SpeedLimit,1) AS NSpeedLimit

  FROM    STREETNAME AS sn 
   INNER JOIN
            STREETHASSTREETNAME AS hn ON 
                     sn.GlobalID = hn.GlobalID AND 
                     hn.Role = 'Primary'
          INNER JOIN      STREETCENTERLINE AS sc ON 
              hn.GlobalID = sc.GlobalID

            ) AS A

SQL Server calculated column using group by

Discussion earlier: How do I Specify Computed Columns in a Table which are based on another Column in SQL Server?

create table orderitems 
(
orderId int,
itemid int,
productId int,
itemprice decimal(18,2),
quantity int
)
GO

create table orders
(
orderId int,
shipAmount decimal(18,2),
TaxAmount decimal(18,2)
)
GO

insert into orderitems (orderId,itemid,productId,itemprice,quantity) values
(1,1,2,1199,1),
(2,2,8,489.99,1),
(3,3,1,2517,1),
(3,4,9,415,1),
(4,5,2,1199,1),
(5,6,10,299,1),
(6,7,10,299,1)
GO

insert into orders (orderId,shipAmount,TaxAmount) values
(1,5,58.71),
(2,5,58.72),
(3,10,58.73),
(4,10,58.74),
(5,5,58.75),
(6,5,58.76);
GO

select * from orders
GO

orderId | shipAmount | TaxAmount
------: | :--------- | :--------
      1 | 5.00       | 58.71    
      2 | 5.00       | 58.72    
      3 | 10.00      | 58.73    
      4 | 10.00      | 58.74    
      5 | 5.00       | 58.75    
      6 | 5.00       | 58.76    

create function udfMyTable (@orderID  as int)
returns decimal(18, 2)
as
begin
  declare @res as decimal(19, 2);

  Select
    @res = SUM(itemprice*Quantity)
  from orderitems
  where
    orderID = @orderID    

return @res;
end
GO

alter table orderitems add SubTotal as dbo.udfMyTable(orderID)
GO

alter table orders add SubTotal as dbo.udfMyTable(orderID)
GO

Select * from orderitems
GO

orderId | itemid | productId | itemprice | quantity | SubTotal
------: | -----: | --------: | :-------- | -------: | :-------
      1 |      1 |         2 | 1199.00   |        1 | 1199.00 
      2 |      2 |         8 | 489.99    |        1 | 489.99  
      3 |      3 |         1 | 2517.00   |        1 | 2932.00 
      3 |      4 |         9 | 415.00    |        1 | 2932.00 
      4 |      5 |         2 | 1199.00   |        1 | 1199.00 
      5 |      6 |        10 | 299.00    |        1 | 299.00  
      6 |      7 |        10 | 299.00    |        1 | 299.00  

select * from orders
GO

orderId | shipAmount | TaxAmount | SubTotal
------: | :--------- | :-------- | :-------
      1 | 5.00       | 58.71     | 1199.00 
      2 | 5.00       | 58.72     | 489.99  
      3 | 10.00      | 58.73     | 2932.00 
      4 | 10.00      | 58.74     | 1199.00 
      5 | 5.00       | 58.75     | 299.00  
      6 | 5.00       | 58.76     | 299.00  

db<>fiddle here

SQL Server - Calculated column Sum from different table

Assuming you are trying to show the available qty for each item type:
I would recommend creating a view that shows this information:

You will need to "GROUP BY" the item type.

CREATE VIEW Item_Types_Qty_In_Stock
AS

SELECT it.ID, SUM(i.Qty_In_Stock) AS QtyInStock FROM Items i
INNER JOIN Item_Types it ON i.Item_Type = it.ID 
GROUP BY it.ID
GO

Once you have created the view, you can query it as if it were a table:

SELECT * FROM Item_Types_Qty_In_Stock

---

Related Topics