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
obtain by all the students. The AVG command will calculate the average
value of all the marks
Here we can use the Group
By command to find out the average mark obtained by each
SELECT class, avg( mark ) as avg_mark FROM `student` GROUP BY class
Please see the SQL SUM command to know the details
on uses of GROUP BY command and the
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
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, sum(social + math + science ) as total, sum(social + math + science )/3 as avg from student3 group by id
My John Rob
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
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
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 chage 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
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