Geek Answers Handbook

Get average value for a column in T-SQL

Ok, so I have a table, and in one column I have some data, and in the second column - the average value of the data. Example

id|Data|avg
1 |20  |20
2 |4   |12
3 |18  |14

How to populate an avg column in an insert with the current value of a Data column using T-SQL?

EDIT: Sorry guys, this was actually a stupid mistake I made. I assumed that I have SQL 2014, but after checking Stephan code and receiving some errors, I returned to confirm and understand that I am using SQL 2008. Sorry for the misinformation. I also updated tags

+4
source share
4 answers

SQL Server <= 2008 OVER(ORDER BY ...) .

CREATE TRIGGER trg_running_avg ON myTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  UPDATE old
    SET avg = new_avg
  FROM myTable old
  CROSS APPLY (
    SELECT AVG(Data) AS new_avg FROM myTable WHERE ID <= old.ID
  ) new
  --Skip the full table update. Start from the lowest ID that was changed.
  WHERE id >= (SELECT MIN(id) FROM (SELECT ID FROM inserted UNION ALL SELECT ID FROM deleted) t)
END
GO

, . , , . .

+3

, id , data:

insert into table t(id, data, avg)
    select @data, @data * (1.0 / n) + avg * (n - 1.0)/n
    from (select count(*) as cnt, avg(data) as avg
          from t
         ) t;

SQL Server 2012+ :

select t.*, avg(data) over (order by id) as cume_avg
from table t

SQL Server 2012 apply:

select t.*,
       (select avg(data)
        from table t2
        where t2.id <= t.id
       ) as cume_avg 
from table t;

, . , id, data .

+6

.

IF OBJECT_ID('myTable') IS NOT NULL
    DROP TABLE myTable;

CREATE TABLE myTable(ID INT, Data INT,[avg] INT);
GO

CREATE TRIGGER trg_running_avg ON myTable
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO myTable
        SELECT ID,Data,AVG(Data) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING)
        FROM inserted
END

INSERT INTO myTable(ID,Data)
VALUES  (1,20),(2,4),(3,18)

SELECT *
FROM myTable


CREATE VIEW vw_average
AS
SELECT ID,Data,AVG(Data) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING)
        FROM inserted


UPDATE myTable
SET avg = running_avg
FROM myTable A
INNER JOIN (SELECT ID,AVG(Data) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) running_avg FROM myTable) B
ON A.ID = B.ID

CTE

WITH CTE_Update
AS 
(
    SELECT  ID,
            [avg] OldAvg,
            AVG(Data) OVER (ORDER BY ID) AS NewAvg
    FROM myTable
) 
UPDATE CTE_Update SET OldAvg = NewAvg
+5
source

I feel this should work with self-join:

select t1.id, t1.data, sum(t2.data)/t1.id as avg
from table t1, table t2
where t1.id>=t2.id group by t1.id

joining will give:

t1.id|t1.Data|t2.id|t2.Data
1    |  20   |  1  |   20
2    |  4    |  1  |   20
2    |  4    |  2  |   4
3    |  18   |  1  |   20
3    |  18   |  2  |   4
3    |  18   |  3  |   18
0
source

More articles:


All Articles