automagical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, JanHi
You will need to do this though a trigger on the table where the insert gets
done.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>|||Hi Jan
You may possibly want to consider using a view for AggregatedSales!
John
"Jan R?sner" wrote:

> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from anothe
r
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates t
he
> value for this field depending on a SELECT-Statement with a SUM() - is tha
t
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
>|||"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
Create a view rather than a summary table. You can consider making it an
indexed view. You should avoid storing calculated results whenever possible.
David Portas
SQL Server MVP
--