CODEWITHSUNDEEP
sqlrdatabasedataframetranspose
mindhabits
mindhabits

Reputation: 421

How to Take Average of Data Within a Column to create new Variable

I have a simple task which I cannot wrap my head around being a novice coder.

I have a data set which I am trying to manipulate.

It appears as this:

UniqueID Day Var AverageVar
1        1   X
1        2   Y
1        3   Z
2        1   A
2        2   B
2        3   C

I would like to create this new "AverageVar" variable which computes an average across the three days for each unique ID.

So, for example, the AverageVar for the first three rows I would like to create and have (X + Y + Z)/3 displayed. Is there any easy code for this in SQL or R?

Upvotes: 1

Views: 176

Answers (1)

chuongpham
chuongpham

Reputation: 66

SELECT * INTO newtable
FROM 
(SELECT UniqueID, AVG(Var) as AverageVar
FROM table
GROUP BY UniqueID);

SELECT O.UniqueID, O.Day, O.Var, N.AverageVar
FROM oldtable O
INNER JOIN
newtable N
ON O.UniqueID = N.UniqueID;

Upvotes: 1

Related Questions