How to Calculate Average of a Table Column in MySQL
In this tutorial, we aim to explore how to calculate the average of a table in MySQL.
Average refers to the sum of all the data points divided by the total number of data points involved. It is important to have all the data point values as integers or floating-point values to calculate the average.
We can also round up or down the average value to an integer according to our requirement in MySQL. Let us try to understand how to calculate the average of a column.
Create a Table in MySQL
Before we begin, we create a dummy dataset to work with. Here we will create a table, student_details, along with a few rows.
-- create the table student_details_average
CREATE TABLE student_details_average(
stu_id int,
stu_firstName varchar(255) DEFAULT NULL,
stu_marks varchar(255) DEFAULT 20,
primary key(stu_id)
);
-- insert rows to the table student_details_average
INSERT INTO student_details_average(stu_id,stu_firstName,stu_marks)
VALUES(1,"Preet",30),
(2,"Rich",35),
(3,"Veron",50),
(4,"Geo",50),
(5,"Hash",20),
(6,"Sachin",25),
(7,"David",40);
The above query creates a table with rows containing the student’s first name and marks in a particular subject out of 50. To view the entries in the data, we use the following code:
SELECT * FROM student_details_average;
The above code would give the following output:
stu_id stu_firstName stu_marks
1 Preet 30
2 Rich 35
3 Veron 50
4 Geo 50
5 Hash 20
6 Sachin 25
7 David 40
Calculate the Average of a Table Column in MySQL
Now we have successfully created and visualized our student_details_average table. Let us try to calculate the average score of the students based on their marks.
This can be done with the help of the following syntax:
SELECT AVG(column_name) AS average FROM Table_name;
As we can see, in the query mentioned above, we need to enter the table and column names to fetch the average value. This task can be achieved using the following query:
SELECT AVG(stu_marks) AS Average FROM student_details_average;
The query above has the database name as boatdb and table name as student_details_average, as mentioned before. The output of the code above is as follows:
Average
35.7142
As we know, the total sum of all scores is 250, and the total number of students within the table is 7, so the average is 35.7142. Thus we have successfully found the average value.
This has been made possible by the built-in AVG function in MySQL that takes the column name as the sole argument. Therefore, with the help of the AVG function, we can efficiently calculate the average associated with a particular column.
