Column Calculated from Another Column

How do I create column calculated from another column?

You should use a computed column to solve this problem. Something with a definition similar to this:

ALTER TABLE Customers ADD Age AS datediff(year, DOB ,getdate())

Original statement taken from and further information available at BlackWasp.

Edit:

MSDN explains computed columns as:

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.

Unless otherwise specified, computed columns are virtual columns that
are not physically stored in the table. Their values are recalculated
every time they are referenced in a query. The Database Engine uses
the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements
to physically store computed columns in the table. Their values are
updated when any columns that are part of their calculation change. By
marking a computed column as PERSISTED, you can create an index on a
computed column that is deterministic but not precise. Additionally,
if a computed column references a CLR function, the Database Engine
cannot verify whether the function is truly deterministic. In this
case, the computed column must be PERSISTED so that indexes can be
created on it. For more information, see Creating Indexes on Computed
Columns.

Computed columns can be used in select lists, WHERE clauses, ORDER BY
clauses, or any other locations in which regular expressions can be
used, with the following exceptions:

Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked
PERSISTED. A computed column can be used as a key column in an index or as part of any
PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a
deterministic expression and the data type of the result is allowed in index
columns.

For example, if the table has integer columns a and b, the computed column a + b can be
indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed because the
value may change > in subsequent invocations.

A computed column cannot be the target of an INSERT or UPDATE statement.

The Database Engine automatically determines the nullability of
computed columns based on the expressions used. The result of most
expressions is considered nullable even if only nonnullable columns
are present, because possible underflows or overflows will produce
null results as well. Use the COLUMNPROPERTY function with the
AllowsNull property to investigate the nullability of any computed
column in a table. An expression that is nullable can be turned into a
nonnullable one by specifying ISNULL(check_expression, constant),
where the constant is a nonnull value substituted for any null result.

Source: MSDN - Computed Columns

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

How to calculate the value of one column based on another column?

First, handle the ranges and select the lower value, then create two boolean masks for k and % separately and then apply all the related logic. For example:

# Handle ranges
df['Bonus'] = df['Bonus'].str.split('-').str[0].str.strip()

# Create boolean masks
ks = df['Bonus'].str.endswith('k')
ps = df['Bonus'].str.endswith('%')

# Remove 'k' and '%' and convert the type to float
df.loc[ks | ps, 'Bonus'] = df.loc[ks | ps, 'Bonus'].str[:-1]
df['Bonus'] = df['Bonus'].astype(float)

# Apply the mask logic and convert to int
df.loc[ks, 'Bonus'] = df.loc[ks, 'Bonus'] * 1000
df.loc[ps, 'Bonus'] = df.loc[ps, 'Total'] * df.loc[ps, 'Bonus'] / 100
df['Bonus'] = df['Bonus'].astype(int)

Result:

    Name  Total  Bonus
0    Amy  15000  10000
1    Bob  14000   1400
2  Cathy  13400   5500
3  David  14800   1480
4   Emma  15200   1216
5    Fay  13800      0
6   Gina  14500   5000

Add a calculated column based on same and two other columns in r

Arrange the data based on descending order of year value and for each id subtract the current value with the next one.

library(dplyr)

dat %>%
  arrange(desc(year)) %>%
  group_by(id) %>%
  mutate(difference = value - lead(value)) %>%
  #to get 0 instead of NA use the below one
  #mutate(difference = value - lead(value, default = last(value))) %>%
  ungroup

#  year    id value difference
#  <dbl> <dbl> <dbl>      <dbl>
#1  2011     1    10         -1
#2  2011     2    20         15
#3  2011     3    30         24
#4  2005     1    11         NA
#5  2005     2     5         NA
#6  2005     3     6         NA

Calculate column value based on another column that is calculated

Aliasing an expression is done through cross apply. Change your FROM to:

FROM 
    cteAccScheduleFull
    cross apply
    (select CASE 
                WHEN Enrolled = 'C' THEN 'Y'
                ELSE 'N' 
            END AS EnrolledDer --Enrolled Derived Column 
    ) as q1

Then you will be able to use q1.EnrolledDer in your select any number of times. You can even chain cross apply blocks if you had more dependent expressions.

How to use a calculated column to calculate another column in the same view

You could use a nested query:

Select
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC as calccolumn2
From (
  Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from t42
);

With a row with values 3, 4, 5 that gives:

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4

You can also just repeat the first calculation, unless it's really doing something expensive (via a function call, say):

Select
  ColumnA,
  ColumnB,
  ColumnA + ColumnB As calccolumn1,
  (ColumnA + ColumnB) / ColumnC As calccolumn2
from t42; 

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4 

---

Related Topics