CODEWITHSUNDEEP
sqlsql-servercalculated-columns
PnP
PnP

Reputation: 3185

SQL computed column for sum of data in another table

I have two tables:

  DiskUsage Table                               StatisticsTable
Server    DiskUsage(GB)                  Total Disk Usage        xxxx
   1         10
   2         212
   3         11

I need to create the "Total Disk Usage" as a column which works out the Sum of the "DiskUsage" column. This would need to be dynamic as more servers will be added overtime to the "DiskUsage" table.

I've done some looking into this - and I believe a Computed Column would be the easiest way to achieve this, but I'm not sure how to a). reference the other tables data or b). dynamically obtain the total of that column.

Upvotes: 1

Views: 2819

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

What is the issue with just running a query?

select sum(diskusage)
from diskusage;

This seems simple enough and unless you have millions of rows, performance should be quite fast.

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

Create a trigger

CREATE TRIGGER test
ON DiskUsage
after INSERT, UPDATE
AS
  BEGIN
      UPDATE StatisticsTable
      SET    TotalDiskUsage = (SELECT Sum(DiskUsage)
                               FROM   DiskUsage)
  END

Or as Mentioned by King.code create a view instead of having a table

CREATE VIEW StatisticsTable
AS
  SELECT Sum(DiskUsage)
  FROM   DiskUsage

Upvotes: 1

Related Questions