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 |
Let us learn about the SUM sql command and how to use it in our tables. SUM command can be applied to numeric field and the total of the value is returned. Now let us apply this SUM command to this table and find out the total mark obtain by all the students. The SUM command will add all the values of the mark field and return to us. This is our table
|
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 |
We will apply the SUM command here like this to the field mark
SELECT sum( mark ) FROM `student`
|
sum(mark) |
|
390 |
The command added all the values of the mark field and displayed. We can define some header like this also.
SELECT SUM(mark) as total_mark FROM `student`
|
total_mark |
|
390 |
Now let us find out what is the total mark in each class. Here we can use the Group By command to find out the total mark obtained by each class
SELECT class, sum( mark ) as total_mark FROM `student` GROUP BY class
|
class |
total_mark |
|
Four |
250 |
|
Three |
140 |
You can see above that total mark of each class is displayed. Since we have two class in our table so the sql command has returned two class with total marks in each of them. We have to use Group By clause if we ask for the query to return any other field name other than the sum. Otherwise system will generate error.
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 sum( mark ) as total_mark, class FROM student where mark > 55 GROUP BY class
|
total_mark |
class |
|
195 |
Four |
|
85 |
Three |
Other sql commands like between can be used along with this sum command to find out required results.