AVG() Function averages the values returned by a specified expression.

      
mysql>
mysql> CREATE TABLE Classes
    -> (
    ->     ClassID SMALLINT NOT NULL PRIMARY KEY,
    ->     Dept CHAR(4) NOT NULL,
    ->     Level ENUM('Upper', 'Lower') NOT NULL,
    ->     TotalStudents TINYINT UNSIGNED NOT NULL
    -> );

mysql> INSERT INTO Classes VALUES
    -> (1001, 'ANTH', 'Upper', 25),
    -> (1002, 'ANTH', 'Upper', 25),
    -> (1003, 'MATH', 'Upper', 18),
    -> (1004, 'ANTH', 'Lower', 19),
    -> (1005, 'ENGL', 'Upper', 28),
    -> (1006, 'MATH', 'Lower', 23),
    -> (1007, 'ENGL', 'Upper', 25),
    -> (1008, 'MATH', 'Lower', 29),
    -> (1009, 'ANTH', 'Upper', 25),
    -> (1010, 'ANTH', 'Lower', 30),
    -> (1011, 'ENGL', 'Lower', 26),
    -> (1012, 'MATH', 'Lower', 22),
    -> (1013, 'ANTH', 'Upper', 27),
    -> (1014, 'ANTH', 'Upper', 21),
    -> (1015, 'ENGL', 'Lower', 25),
    -> (1016, 'ENGL', 'Upper', 32);

mysql>
mysql>
mysql> SELECT Dept, Level, ROUND(AVG(TotalStudents)) AS Average
    -> FROM Classes
    -> GROUP BY Dept, Level WITH ROLLUP;
+------+-------+---------+
| Dept | Level | Average |
+------+-------+---------+
| ANTH | Upper |      25 |
| ANTH | Lower |      25 |
| ANTH | NULL  |      25 |
| ENGL | Upper |      28 |
| ENGL | Lower |      26 |
| ENGL | NULL  |      27 |
| MATH | Upper |      18 |
| MATH | Lower |      25 |
| MATH | NULL  |      23 |
| NULL | NULL  |      25 |
+------+-------+---------+
10 rows in set (0.00 sec)

mysql>
mysql>
mysql> drop table Classes;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	To display an average value of zero in that case, modify the query to test the value of AVG( ) with IFNULL( )
2.	Returning the Average, Minimum, and Total Values with AVG( ), MIN( ), and SUM( )
3.	Get the average price
4.	Average length
5.	How much you paid for each author's books, in total and on average
6.	Find the average sales amount per sales quarter.
7.	How many miles did the drivers in the mytable table travel? What was the average miles traveled per day?

AVG() Function averages the values returned by a specified expression. : AVG « Aggregate Functions « SQL / MySQL

Related examples in the same category