I have the following fabricated data seen below.
I would like to alter the Total_Investment Column to make it a Computed Column which computes the total Dollar_Amount for the Unit Name that is present in that row.
For example, for Row 1, the Total_Investment slot would show the sum of both rows that are listed as 'Healthy_Communities', so rows 1 and 6, eg., 5899.00 + 1766.00.
Row 2 would sum all three (2, 5, and 9) of the Urban entries, and so on. How do I accomplish this in SQL Sever 2012?
Project_ID Unit_Name Fiscal_Year Dollar_Amount Total_Investment
1 Healthy Communities 2000-01-01 5899.00 0.00
2 Urban 2009-01-01 6008.00 0.00
3 Rivers and Watersheds 2006-01-01 6835.00 0.00
4 Large Landscapes 2011-01-01 5216.00 0.00
5 Urban 2015-01-01 3555.00 0.00
6 Healthy Communities 2014-01-01 1766.00 0.00
7 Youth Engagement 2004-01-01 4246.00 0.00
8 Rivers and Watersheds 2014-01-01 8253.00 0.00
9 Urban 2000-01-01 5590.00 0.00
10 Outdoor Recreation 2013-01-01 5356.00 0.00
I'm aware of the alter table documentation for computing columns but don't know how to modify this code to fit my problem.
Only replace MyTable below with the name of your table. The drawback of this method is that it becomes really expensive if working with many rows (e.g. 40 s for 10000). For such a case, you can use a view. (Thanks to @Amit for insisting on the UDF. Sorry, the performance is aweful)
Edit: Added nonclustered index on
Unit_Name
, includingDollar_Amount
. Performance improved tenfold.