SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Math Functions

SQL AVG Command

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

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

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

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

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

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.

Number of User Comments : 3


Google+

lotr04-04-2009
Note that on some mysql servers the autorounding is bad handled ...
chandradevi23-01-2013
it is good
Karthikeyan.M14-02-2013
order a class students by mark and list out by max,aveg
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked




HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer