Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Aggregate SQL

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

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"

Former Member
Not what you were looking for? View more on this topic or Ask a question