AVG() : Average value of a Column

SQL Average 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

  Same way we can get the minimum value of a range of records by using SQL MIN command. Also check up SQL MAX command to get highest value of data
We will apply the AVG command here like this to the field  mark
SELECT avg( mark ) FROM `student`
avg(mark)
65.0000
The command will calculate average value of marks considering all the marks of the table. We can define some header  like this also.
SELECT AVG(mark) as avg_mark FROM `student`
avg_mark
65.0000

SQL AVG with Subquery

We can prepare a list of students who has got less than average mark in a class
SELECT * FROM student WHERE class='Six' and 
  mark <(SELECT AVG(mark) FROM `student` where class='Six')
idnameclassmarksex
9Tes QrySix78male
17TumyuSix54male
30Reppy RedSix79female

Read more SQL Sub Queries


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 
classAVG(mark)
Eight79.0000
Five79.3333
Seven77.6000
Six82.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
idnamesocialmathsciencetotalavg
2 Max Ruin 85 85 56 22675.333
3 Arnold5575 40 17056.6667
4 Krish Star60 70 50 18060.0000
5 John Mike60908023076.6667
6 Alex John55809022575.0000
7 My John Rob78706020869.3333
8Asruid85 908025585.000
9Tes Qry78706020869.3333
10Big John 55 554015050.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.

You can download the MySQL dump of the table with sample data here.

Adding average mark of each student.
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.

Read more on DISTINCT Query


CREATE TABLE IF NOT EXISTS `plus2_price` (
  `product` varchar(10) NOT NULL,
  `date` date NOT NULL,
  `Price` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `plus2_price`
--

INSERT INTO `plus2_price` (`product`, `date`, `Price`) VALUES
('Product1', '2017-02-24', 10),
('Product1', '2017-02-24', 10),
('Product1', '2017-02-24', 10),
('Product1', '2017-02-25', 20);

Inserting Average mark in a new table.

We can store the average mark of each student in a new table for report generation.

create table with average mark of student

Average with null data

If there are some null data then how that change the average value?
Here is an example
StudentMark
Max Ruin50
ArnoldNULL
Krish StarNULL
John Mike50
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
Download the SQL dump of the above student table
Other SQL commands like between can be used along with this avg command to find out required results.
SQL Math References SQL SUM COUNT() Maximum value Query
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    lotr

    04-04-2009

    Note that on some mysql servers the autorounding is bad handled ...
    chandradevi

    23-01-2013

    it is good
    Karthikeyan.M

    14-02-2013

    order a class students by mark and list out by max,aveg
    chika Augustine

    07-06-2016

    Pleas I want to check average for the three terms for promotion of class
    Yahaya Abdulkadir

    26-08-2017

    please I would like to know how to sort a database student average score in result processing?
    smo1234

    31-10-2017

    Use order by query

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer