Create a new column with an Expression

Sometimes there may be a requirement to create a new column based upon existing columns with a minor manipulation.

For example in the AdventureWorks database the table TransactionHistoryArchive shows the quantity and the actual cost of each unit but not the total value of the order.

That would be achieved by multiplying the Quantity and AcutalCost columns.

Current results from the table are below.


SELECT [TransactionID]

,[ProductID]

,[ReferenceOrderID]

,[ReferenceOrderLineID]

,[TransactionDate]

,[TransactionType]

,[Quantity]

,[ActualCost]

,[ModifiedDate]

FROM [AdventureWorks].[Production].[TransactionHistoryArchive]

1

However we need to multiply Quantity * ActualCost  and call the column TotalOrderValue.


SELECT [TransactionID]

,[ProductID]

,[ReferenceOrderID]

,[ReferenceOrderLineID]

,[TransactionDate]

,[TransactionType]

,[Quantity]

,[ActualCost]

,[ModifiedDate]

,[Quantity] * [ActualCost] AS TotalOrderValue

FROM [AdventureWorks].[Production].[TransactionHistoryArchive]

2

Leave a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: