automagical calculation of a field value

12 Nov 2005 12:56 PM

Jan Rösner

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

12 Nov 2005 1:06 PM

Mike Epprecht (SQL MVP)

Hi

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: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"Jan Rösner" <jan.roes***@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
>

12 Nov 2005 6:01 PM

John Bell

Hi Jan

You may possibly want to consider using a view for AggregatedSales!

John

Show quote

"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 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
>
>
>

12 Nov 2005 6:15 PM

David Portas

Show quote

"Jan Rösner" <jan.roes***@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
--