We often need to calculate average results. When it comes to education, it is a daily job to calculate exam grades, report statistics of average results, and how students have performed. If you have a database with a list of students and their results of multiple subject exams, it is more challenging - and often the first step, to conduct reports and group results by different criteria than calculating a mean, median or other indexes.
In this tutorial, I'll show you how to use MySQL queries and functions to create reports for exam scores and then calculate averages - weighted arithmetic mean and ordinary arithmetic mean. Of course, you can use the two sample databases and the scripts to store data and build reports for other purposes like employee management, vehicle or product tests, hardware performance, etc.
We have two MySQL tables - students and results. In the "students" table we hold information for all students - name and unique ID. You can add any other fields to this table such as phone, email, address, etc. To make it simple, we only have two fields. (See
demo data)
CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL
)
In the "results" table we have 4 fields:
- unique auto-increment ID
- student_id used to refer to student from "students" table
- result - the exam result
- subject - the exam subject
This is the CREATE query for the table. (See
demo data)
CREATE TABLE `results` (
`id` int(11) NOT NULL,
`student_id` int(5) DEFAULT NULL,
`result` varchar(10) DEFAULT NULL,
`subject` varchar(9) DEFAULT NULL
)
Now there are several different reports that we may run against this data:
1) the number of students who took an exam for each subject. Example: 50 students took the exam with excellence
2) the number of students with specific result for a particular subject. Example: 25 students have excellence in History
3) list of students who took exam on a specific subject and had a specific result
4) list of students who took more than X exams
5) all exams and results for a student
6) percentage for each result from total exams for a specific subject
7) average exams results for a specific subject
There are all kinds of SQL reports that can be executed. If you need another report, please, comment below, and we will help you generate it!
Report #1 (
demo report)
Let's count all results from all exams and group them by subject! This will create a table with all subjects and number of exams taken for each subject by all students.
SELECT subject, COUNT(*) FROM `results` GROUP BY subject
Report #2 (
demo report)
If you want to check the results for a specific subject (in our example this will be History), we need a SELECT query to count all results for that subject and group them by result.
SELECT COUNT(*), subject, result FROM `results`
WHERE subject='History' GROUP BY result
Report #3 (
demo report)
Now let's see how many students have an excellent result in History! We will use INNER JOIN in the SELECT query so we can match student_id from the results table with student name from students table. In the WHERE clause, we will specify that we only need records that have subject History and an excellent result.
SELECT students.name FROM `results`
INNER JOIN students ON results.student_id=students.id
WHERE results.subject='History' and results.result='excellence'
GROUP BY students.name
Report #4 (
demo report)
Using the following SELECT query, we will list all students who took more than 8 exams. We will also sort the results in an ascending order.
SELECT students.name, count(results.id) FROM `results`
INNER JOIN students ON results.student_id=students.id
GROUP BY results.student_id
HAVING count(results.id) > 8
ORDER BY count(results.id) ASC
Report #5 (
demo report)
The report will show all exams and results for a specific student. As we will use student name for the SQL query, we need to use JOIN in the SELECT so we can match student name.
SELECT result, subject FROM `results`
INNER JOIN students ON students.id=results.student_id
WHERE students.name="Martin Jackson"
ORDER BY results.subject, results.id ASC
Report #6 (
demo report)
Once we have grouped the data we can calculate averages and other statistic indexes. With the following query we will see the percent of each different result from all exams taken for a specific subject.
SELECT result, count(*),
concat(round(( count(*)/(SELECT count(*) FROM results WHERE subject='History') * 100 ),2),'%') AS percentage
FROM results
WHERE subject='History' GROUP BY result
Report #7
This last report is the most complex one. It will show the weighted average results for a specific subject. Using the results from Report 6 we know total number of exams for History and number of each result.
excellence 13 15.48%
acceptable 23 27.38%
good 21 25.00%
poor 13 15.48%
failing 14 16.67%
Total number of taken exams is 84. We will replace each of the results with a numeric value (excellence = 5, acceptable = 4, good = 3, failing = 2, poor = 1). Then we will multiply number of exams for each result by its numerical value and will sum them all: 13 * 5 + 23 * 4 + 21 * 3 + 14 * 2 + 13 * 1 = 261. Now what we need to do is to divide that number by the number of taken exams: 264 / 84 = 3.11. So the weighted average result for History is "good".
You can also check the tutorial "
Select Data And Split On Pages" to see how to split the results in multiple pages and download "
Free MySQL Table Search script" to learn how to create a search.