How to set a calculated column using a subquery

Question

I have a table to which I would like to add a calculated column. The query I want to set it to is more complex than a standard arithmetic operation and I am unsure how to set up the calculated column using the query. I attempted to use an ALTER TABLE statement:

    ALTER TABLE shareholder.Amount
ADD CalculatedAmount As 
(SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)

But this results in an error: 'Subqueries are not allowed in this context. Only scalar expressions are allowed'. I know the sub-query itself works correctly having tested it on its own so it's just a matter of working out how to set the calculated column to be the result of it.

Thanks! (I am using SQL Server 2014 Management Studio)

well.....as the error message says, subqueries are not allowed in computed columns — Lamak, Jan 24 '18 at 12:56

score 3 · Accepted Answer · answered Jan 24 '18 at 12:57

It is not possible to have a Computed Column with a Sub Query,

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

So it is not possible to have A Query but you can use Expressions Like

ColumnA-ColumnB+ColumnC

Instead, you can convert it as a View and Compute The Column values there

Like this

CREATE VIEW MyComputedvIEW
AS
SELECT
  *,
  CalculatedAmount = (SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)
FROM YourTable

Ah ok, I didn't appreciate that calculated columns can only be referencing the same table. Thanks. — D.Yvel, Jan 24 '18 at 13:12
Calculated Column Can Be Based on other tables but in that Case, you will have to use a Scalar Function @D.Yvel — Jayasurya Satheesh, Jan 24 '18 at 13:18

score 3 · Answer 2 · answered Mar 23 '20 at 14:30

It is possible to add subquery with a little trick (UDF), see my example ([ChildCount] field):

CREATE TABLE [wp].[StorageData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [StorageHid] [hierarchyid] NOT NULL,
    [StoragePath]  AS ([StorageHid].[ToString]()),
    [StorageLevel]  AS ([StorageHid].[GetLevel]()),
    [StorageParentHid]  AS ([StorageHid].[GetAncestor]((1))),
    [StorageParent]  AS ([StorageHid].[GetAncestor]((1)).ToString()),
    [ChildCount]  AS ([wp].[GetStorageDataChildItemCount]([StorageHid].[ToString]()))
)

CREATE FUNCTION [wp].[GetStorageDataChildItemCount]
(
    @storagePath NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN

    DECLARE @ret INT = 0;
    SET @ret = (SELECT COUNT(ID) FROM [wp].[StorageData] R WHERE R.StorageParent = @storagePath)
    RETURN @ret;

END

How to set a calculated column using a subquery

2 Answers2