Skip to Content
avatar image
Former Member

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"

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Sep 12, 2014 at 06:08 AM

    Hi Ashish,

    Are you looking to implement at report level the logic you have shown in the query you have provided?

    If so, you can follow below steps:

    1. Create groups on year, week and student columns. So, your first group will be on year, 2nd group will be on week and third will be on student.

    2. Move all your fields from detail section to group footer 3 section and In group footer 3, insert a summary and select average from sum dropdown, which will give the average of the records for each student based on week and year and you can use this instead of your databse field for "Avg completion time" .

    Hope this is what you are looking for.

    Thanks,

    Raghavendra

    Add comment
    10|10000 characters needed characters exceeded