SQL Server 2005 Computed Column Result From Aggregate Of Another Table Field's Value

Question

Sorry for the long question title.

I guess I'm on to a loser on this one but on the off chance.

Is it possible to make the calculation of a calculated field in a table the result of an aggregate function applied to a field in another table.

i.e.

You have a table called 'mug', this has a child called 'color' (which makes my UK head hurt but the vendor is from the US, what you going to do?) and this, in turn, has a child called 'size'. Each table has a field called sold.

The size.sold increments by 1 for every mug of a particular colour and size sold.

You want color.sold to be an aggregate of SUM size.sold WHERE size.colorid = color.colorid

You want mug.sold to be an aggregate of SUM color.sold WHERE color.mugid = mug.mugid

Is there anyway to make mug.sold and color.sold just work themselves out or am I going to have to go mucking about with triggers?

Answer 1

No, it is not possible to do this. A computed column can only be derived from the values of other fields on the same row. To calculate an aggregate off another table you need to create a view.

If your application needs to show the statistics ask the following questions:

1. Is it really necessary to show this in real time? If so, why? If it is really necesary to do this, then you would have to use triggers to update a table. This links to a short wikipedia article on denormalisation. Triggers will affect write performance on table updates and relies on the triggers being active.
2. If it is only necessary for reporting purposes, you could do the calculation in a view or a report.
3. If it is necessary to support frequent ad-hoc reports you may be into the realms of a data mart and overnight ETL process.

Answer 2

you can't have a computed column directly reference a different table, but you can have it reference a user defined function. here's a link to a example of implementing a solution like this.

http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/