Aggregation MS SQL

[amishraa]

How do I get average of the duration for multiple weeks.

Currently I get something like

Student Avg completion time Week Year

STUDENT 1 0 1 2013

STUDENT 1 5 1 2013

What I want is

Student Avg completion time Week Year

STUDENT 1 2.5 1 2013

<code>
select a.student,avg(a.duration) as "Avg Completion Duration","Week","Year" from (

select a1.student,a1.timecomplete,datepart(week,a1.completiondate) as "Week",datepart(year,a1.completiondate) as "Year" from attendance a1

where year(a1.datetimein)=year(getdate())

union all

select a2.student,a2.timecomplete,datepart(week,a2.completiondate) as "Week",datepart(year,a2.completiondate) as "Year" from attendance a2

where year(a2.datetimein)=year(getdate())-1

group by a2.student,a2.timecomplete,datepart(week,a2.completiondate),datepart(year,a2.completiondate)

) a

group by "Week", a.student,"Avg Completion Duration","Year"
</code>

[adam_k]

I'm not sure why you group by "Avg Completion Duration". That means that you want a separate record for each completion duration.
If you want the average to include results from several weeks then you also need to remove the week from the group by AND the select list. The reason here is that the average can't "belong" to a specific week unless it is calculated per week (which would mean adding it to the group by).

To sum up, you only group by with the columns that you don't want aggregation and you group by the columns that you want the aggregation to take place for each value of them. In example if you group by year then you want the average for each year, if you group by year, student then you want the average for each year and for each student,...

Good luck.