## creating mean variable by groups

Solved
Occasional Contributor
Posts: 6

# creating mean variable by groups

I apologize in advance if this question is silly, but how do I create a mean variable by groups. Take the below table for example, I want to calculate a Var2 such that for the first 3 observations, Var2 is the mean(Var1 of the first 3 obs), and Var2 for the next 7 obs is mean(Var1 from id=4 to id=10). ie, to get from Table 1 to Table 2. Thank you!

 Table 1 ID Group Var1 1 A 1 2 A 1 3 A 4 4 B 3 5 B 3 6 B 3 7 B 3 8 B 3 9 B 3 10 B 10

 Table 2 ID Group Var1 Var2 1 A 1 2 2 A 1 2 3 A 4 2 4 B 3 4 5 B 3 4 6 B 3 4 7 B 3 4 8 B 3 4 9 B 3 4 10 B 10 4

Accepted Solutions
Solution
‎02-16-2012 03:06 AM
Super User
Posts: 5,882

## creating mean variable by groups

SQL with a GROUP BY that is remerging the statistic with the original query:

select group, var1, mean(var1) as var2

from table1

group by group;

Data never sleeps

All Replies
Solution
‎02-16-2012 03:06 AM
Super User
Posts: 5,882

## creating mean variable by groups

SQL with a GROUP BY that is remerging the statistic with the original query:

select group, var1, mean(var1) as var2

from table1

group by group;

Data never sleeps
🔒 This topic is solved and locked.