AVG SQL command is used to find out average value of a numeric field in a
table. Now let us apply this AVG command to this table and find out the
average mark
obtain by all the students. The AVG command will calculate the average
value of all the marks
id
name
class
mark
1
John Deo
Four
75
2
Max Ruin
Three
85
3
Arnold
Three
55
4
Krish Star
Four
60
5
John Mike
Four
60
6
Alex John
Four
55
MySQL query to get Average value of a column by using avg() along with GROUP BY , in and Having
Here we can use the Group
By command to find out the average mark obtained by each
classes.
SELECT class, avg( mark ) as avg_mark
FROM `student` GROUP BY class
class
avg_mark
Four
62.5000
Three
70.0000
Please see the SQL SUM command to know the details
on uses of GROUP BY command and the
precautions.
We can add condition to the SQL command to get our desired result. We can add
one Where clause to the query to consider records
for which mark is more than some value ( say 55 )
SELECT avg( mark ) as avg_mark, class FROM student
WHERE mark > 55 GROUP BY class
avg_mark
class
65.0000
Four
85.0000
Three
Using HAVING
SELECT all classes with average mark for which class average mark is more than 75 . Read more on HAVING Query
SELECT class, AVG(mark) from student GROUP BY class
HAVING AVG(mark) >75
class
AVG(mark)
Eight
79.0000
Five
79.3333
Seven
77.6000
Six
82.5714
Average mark in all subjects ( columns ) of a student ( row )
Now we need to calculate average mark of each student. Similarly we will display the total mark of each student with marks in all subjects. For this we will use different student table and dump of this table is available at the end of the page.
Here is the query.
SELECT id,name, social, math, science,
(social + math + science) as total,
(social + math + science )/3 as avg
from student3
id
name
social
math
science
total
avg
2
Max Ruin
85
85
56
226
75.333
3
Arnold
55
75
40
170
56.6667
4
Krish Star
60
70
50
180
60.0000
5
John Mike
60
90
80
230
76.6667
6
Alex John
55
80
90
225
75.0000
7
My John Rob
78
70
60
208
69.3333
8
Asruid
85
90
80
255
85.000
9
Tes Qry
78
70
60
208
69.3333
10
Big John
55
55
40
150
50.0000
Updating average mark of each student and class average mark in same table.
Against each student we will store average mark of the student in three subjects and also the class average mark.
We will use LEFT JOIN to join same table along with GROUP BY Query command to get class average.
update student3_avg a
left join (select id, sum(social + math + science )/3 as number
from student3_avg group by id) as b on a.id=b.id
set a.average=b.number
We used LEFT JOIN to join the same table and get the average mark of each student. Now we will store the class average against each student by using GROUP BY Query.
update student3_avg a
left join (select class, avg(average) as number
from student3_avg group by class) as b on a.class=b.class
set a.average_class=b.number
AVG with DISTINCT Query
We have one table with product and price. There are four records (entries ) out of which three times we paid price at 10 ( on Day 1) and one time paid 20 ( Next Day ) . The average price of this column will be 12.5 as per this query .
SELECT AVG(price) FROM plus2_price
This is the average value of ( 10,10,10,20 ) which is 12.5. But average price of two days is not 12.5 , so now we will change the query by taking unique price by using DISTINCT query.
SELECT AVG(DISTINCT price) FROM plus2_price
The output is 15. Now we get the correct output as average price considering first day and second day price. This is equal to average of 10 & 20.
If there are some null data then how that change the average value?
Here is an example
Student
Mark
Max Ruin
50
Arnold
NULL
Krish Star
NULL
John Mike
50
The average value of 4 students in social subject is 50, ( it is not equal to 100 / 4 =25 ). Note that null data is not same as 0 data. Null data is ignored in average calculation and removed from total number of records.
Displaying from highest to lowest order
We can display records starting with highest total mark and following with next total mark and ending with lowest mark. We will use Order by SQL for this.
SELECT id,name, social, math, science,
sum(social + math + science ) as total,
sum(social + math + science )/3 as avg from student3
group by id order by total DESC
Sample PHP code to display records
Here is the sample code to use the above SQL and display the records. Here we have already connected to MySQL database.
<?Php
require "config.php"; // Database connection
$count="SELECT id,name, social, math, science, sum(social + math + science ) as total, sum(social + math + science )/3 as avg from student3 group by id";
echo "<table>";
echo "<tr><th>id</th><th>name</th><th>social</th><th>math</th><th>science</th><th>total</th><th>avg</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[social]</td><td>$row[math]</td><td>$row[science]</td><td>$row[total]</td><td>$row[avg]</td></tr>";
}
echo "</table>";
?>
We can add SQL FORMAT to restrict the decimal places in above query. Here is the modified Query.
SELECT id,name, social, math, science,
sum(social + math + science ) as total,
FORMAT(sum(social + math + science )/3,2) as avg
from student3 group by id