computed column with aggregate function

Question

I have seven columns in my table: hours, weight, status, total_hours, total_weight and percentage

total_weight = weight where status = 'X'
total_hours = hours where status = 'X'
percentage = total_hours/sum(weight)

sum(weight) is an aggregate function

I would like to specify formula to generate the three computed columns. What do i do?

Answer 1

From what you're saying, you want 3 columns to be aggregations of data across the entire table? SQL Server won't allow this because aggregate functions cannot be used in computed columns. Now you could possibly have user defined scalar functions return the values in the columns, but this would not be able to be persisted and I see little value in such a case.