Use sum function in calculated column

Question

Is it possible to use a sum function in a calculated column? If yes, I would like to create a calculated column, that calculates the sum of a column in the same table where the date is smaller than the date of this entry. is this possible? And last, would this optimize repeated calls on this value over the exemplified view below?

SELECT ProductGroup, SalesDate, (
  SELECT SUM(Sales)
  FROM SomeList
  WHERE (ProductGroup= KVU.ProductGroup) AND (SalesDate<= KVU.SalesDate)) AS cumulated
FROM SomeList AS KVU

score 0 · Accepted Answer · answered Jul 29 '19 at 18:03

Is it possible to use a sum function in a calculated column?

Yes, it's possible using a scalar valued function (scalar UDF) for you computed column but this would be a disaster. Using scalar UDFs for computed columns destroy performance. Adding a scalar UDF that accesses data (which would be required here) makes things even worse.

It sounds to me like you just need a good ol' fashioned index to speed things up. First some sample data:

IF OBJECT_ID('dbo.somelist','U')    IS NOT NULL DROP TABLE dbo.somelist;
GO

CREATE TABLE dbo.somelist
(
  ProductGroup INT           NOT NULL,
  [Month]      TINYINT       NOT NULL CHECK ([Month] <= 12),
  Sales        DECIMAL(10,2) NOT NULL
);

INSERT dbo.somelist 
VALUES (1,1,22),(2,1,45),(2,1,25),(2,1,19),(1,2,100),(1,2,200),(2,2,50.55);

and the correct index:

CREATE NONCLUSTERED INDEX nc_somelist ON dbo.somelist(ProductGroup,[Month])
INCLUDE (Sales);

With this index in place this query would be extremely efficient:

SELECT   s.ProductGroup, s.[Month], SUM(s.Sales)
FROM     dbo.somelist AS s
GROUP BY s.ProductGroup, s.[Month];

If you needed to get a COUNT by month & product group you could create an indexed view like so:

CREATE VIEW dbo.vw_somelist WITH SCHEMABINDING AS 
SELECT   s.ProductGroup, s.[Month], TotalSales = COUNT_BIG(*)
FROM     dbo.somelist AS s
GROUP BY s.ProductGroup, s.[Month];
GO

CREATE UNIQUE CLUSTERED INDEX uq_cl__vw_somelist ON dbo.vw_somelist(ProductGroup, [Month]);

Once that indexed view was in place your COUNTs would be pre-aggregated. You cannot, however, include SUM in an indexed view.

Hey Alan, thank you for your answer. I already have an index as you describe, but the performance is quite low. I did not try an indexed view, because I thought this is not possible. Can you change your view-example to match my select statement? — Krahmi, Jul 30 '19 at 07:51
I changed month to date in my example, because month was misleading — Krahmi, Jul 30 '19 at 08:20

Use sum function in calculated column

1 Answers1