Semester / year average

General
1

Hello,

I’ma trying to set up your brilliant system for university (a bit different academic rules than Gibbon was designed for - I know). I would like to know if there is a way to have a year marks average counted for each student? I have already set up courses and classes, teachers use markbook to put marks within their own class (sunject tought) during each semester, and then - after each semester / year - their put their class (subject) final mark for each student via Internal Assessment. So, as a university admin I have each student having several marks after each semester / year (one final mark from each subject). And now I would like to get each student yearly average - is it possible to do it with the help of query builder? Or export all Internal Assessments to Excel, and then use excel formulas? Please let me know what do you think, I’d be happy to buy the Expert Support as Gibbon is going to be part of my everyday work :slight_smile:

2

Hi Łukasz, great to hear you’re using Gibbon for your organization and thank you for your kind words :smiley:

Yes, this is something that can be done with Query Builder, as long as the final marks are not weighted it can calculate the averages. You could also alternatively use Query Builder to extract the Internal Assessment values to an excel sheet and work with them from there. The value added license for Query Builder includes several useful queries, one of which is “Internal Assessment Results In The Specified Year”. We’d be happy to add a modified version of this to export marks by semester as well.

Here is an example of a query that can give you averages for internal assessment grades per year (it uses one variable called schoolYear which would need to be added via the Query Builder interface or replaced with a database ID):

`SELECT 
    gibbonSchoolYear.name as schoolYear,
    gibbonYearGroup.name AS yearGroup, 
    surname, preferredName, 
    COUNT(DISTINCT gibbonInternalAssessmentEntryID) as `gradeCount`,
    SUM(attainmentValue) as `gradeTotal`, 
    ROUND(SUM(attainmentValue) / COUNT(DISTINCT gibbonInternalAssessmentEntryID), 2) as `gradeAverage`
FROM gibbonPerson 
	JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID) 
	JOIN gibbonYearGroup ON (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID) 
	JOIN gibbonInternalAssessmentEntry ON (gibbonInternalAssessmentEntry.gibbonPersonIDStudent=gibbonPerson.gibbonPersonID) 
	JOIN gibbonInternalAssessmentColumn ON (gibbonInternalAssessmentEntry.gibbonInternalAssessmentColumnID=gibbonInternalAssessmentColumn.gibbonInternalAssessmentColumnID)
	JOIN gibbonCourseClass ON (gibbonInternalAssessmentColumn.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
	JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID)
    JOIN gibbonSchoolYear ON (gibbonStudentEnrolment.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID)
WHERE 
    gibbonCourse.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID
    AND gibbonPerson.status='Full'
    AND gibbonCourse.gibbonSchoolYearID=:schoolYear
GROUP BY gibbonInternalAssessmentEntry.gibbonPersonIDStudent
ORDER BY 
    yearGroup, surname, preferredName`
```
3

Hi Sandra,

I’m sorry for my late reply. Thank you for your positive answer - the best part of it is for me this one: “Yes, this is something that can be done” :slight_smile: This make me feel that the choice of Gibbon was one of the best choices I’ve made last year :slight_smile: Thank you for your help!

4

Dear Sandra,

In case of my undergraduate school, Gibbon will mostly hold mid-term and final-term exam marks (already set as the 2-5 scale (2, 3, 3.5, 4, 4.5, 5)), for every year of studying (3 years programme). So every student studies several subjects (courses) every year and has to pass the exam of each of these subjects either after first term or after second term each year (term = 5 months). Each subject exam has two “approaches” in the meaning of tries - if a student fails for the first time, he can enter the exam in “correction session” (the second try, second approach, second time). So, some students may have two marks from one exam - one negtive mark (2) and one positive, passing mark (3-5).

As I understand, these mid-term and final-term exam marks should be entered (or imported-?) into Gibbon as Internal Assessment. I can make use of Attainment and Effort colums as these two possible tries for everey student I mentiooned above: Attainment - first term of exam (first try), Effort - second term of exam (correction term). Is it ok?

But my most important question is: can Gibbon via Query Builder count the year-average for each student, just as you described via query above, but including both possible exam terms marks?

Example: 3 exams of 3 subject within one year (E1, E2, E3).
Results for two students are:
Student 1: E1=3 (passed in first try), E2=4 (passed in first try), E3=4.5 (passed in first try). Year average: 11,5/3 = 3,83
Student 2: E1=3 (passed in first try), E2=2 and 4 (failed in first try, passed in second try), E3=4.5 (passed in first try). Year average: 13,5/4 = 3,37

Quite a long question, but typical for European colleges and universities average counting method. Hopefully, the anwer could be really short: Yes, it can :slight_smile:

5

Hi @ross , could you please help with the above - answering just yes/no would be absolutely fine for now, so I could keep going with Gibbon for my undergraduate school? Have a nice day :slight_smile:

6

Hi Sandra, of course I understand you are busy at the moment, as the Gibbon v25 just arrived :slight_smile: I managed to resolve that particular case by maself - please take a look below. I will leave it here, just in case anyone else needed to count average this way.

SELECT
gibbonSchoolYear.name as schoolYear,
gibbonYearGroup.name AS yearGroup,
surname, preferredName,
COUNT(DISTINCT attainmentValue) + COUNT(DISTINCT effortValue) as gradeCount,
SUM(attainmentValue + effortValue) as gradeTotal,
ROUND(SUM(attainmentValue + effortValue) / (COUNT(DISTINCT attainmentValue) + COUNT(DISTINCT effortValue)), 2) as gradeAverage
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID)
JOIN gibbonYearGroup ON (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID)
JOIN gibbonInternalAssessmentEntry ON (gibbonInternalAssessmentEntry.gibbonPersonIDStudent=gibbonPerson.gibbonPersonID)
JOIN gibbonInternalAssessmentColumn ON (gibbonInternalAssessmentEntry.gibbonInternalAssessmentColumnID=gibbonInternalAssessmentColumn.gibbonInternalAssessmentColumnID)
JOIN gibbonCourseClass ON (gibbonInternalAssessmentColumn.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID)
JOIN gibbonSchoolYear ON (gibbonStudentEnrolment.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID)
WHERE
gibbonCourse.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID
AND gibbonPerson.status=‘Full’
AND gibbonCourse.gibbonSchoolYearID=:schoolYear
GROUP BY gibbonInternalAssessmentEntry.gibbonPersonIDStudent
ORDER BY
yearGroup, surname, preferredName

7

Thanks Łukasz for sharing your solution, this could certainly help anyone looking to do the same type of calculations. It has indeed been busy with the v25 release. The forums are supported on a volunteer bases, currently mostly by myself, along with a teaching job and developing Gibbon. I try my best to respond to posts several times a week, but can’t guarantee quick responses, given my limited capacity.

8

Hi Sandra,

Today I found one mistake in above script. As sometimes (im my case) the effortvalue may be empty, so this empty cell should not be counted. Bellow is the proper way to count the average, inc. the correct statement “COUNT(DISTINCT NULLIF(effortValue,‘’)) as gradeCount”:

SELECT
gibbonSchoolYear.name as schoolYear,
gibbonYearGroup.name AS yearGroup,
surname, preferredName,
COUNT(DISTINCT attainmentValue) + COUNT(DISTINCT NULLIF(effortValue,‘’)) as gradeCount,
SUM(attainmentValue + effortValue) as gradeTotal,
ROUND(SUM(attainmentValue + effortValue) / (COUNT(DISTINCT attainmentValue) + COUNT(DISTINCT NULLIF(effortValue,‘’))), 2) as gradeAverage
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID)
JOIN gibbonYearGroup ON (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID)
JOIN gibbonInternalAssessmentEntry ON (gibbonInternalAssessmentEntry.gibbonPersonIDStudent=gibbonPerson.gibbonPersonID)
JOIN gibbonInternalAssessmentColumn ON (gibbonInternalAssessmentEntry.gibbonInternalAssessmentColumnID=gibbonInternalAssessmentColumn.gibbonInternalAssessmentColumnID)
JOIN gibbonCourseClass ON (gibbonInternalAssessmentColumn.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID)
JOIN gibbonSchoolYear ON (gibbonStudentEnrolment.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID)
WHERE
gibbonCourse.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID
AND gibbonPerson.status=‘Full’
AND gibbonCourse.gibbonSchoolYearID=:schoolYear
GROUP BY gibbonInternalAssessmentEntry.gibbonPersonIDStudent
ORDER BY
yearGroup, surname, preferredName