MySQL Average AVG()

The AVG() aggregation function in the SQL language is used to calculate the average value over a set of non-zero numeric records.
 

Syntax:

The syntax for using this function is simple:

SELECT AVG(column_name) FROM table_name;

This query calculates the average score of the column “column_name” on all the records of the table “table_name”. It is possible to filter the concerned records using the WHERE command. It is also possible to use the GROUP BY command to group the data belonging to the same entity.
 

 

Example :

Let’s assume the “Orders” table that represents all the orders on an e-commerce site, in which we record the prices, the category, the stock, …

+------+----------+------------+-------+---------+
|  id  |   name   | category   | stock |  price  |
+------+----------+------------+-------+---------+
|  101 | RAM      | computer   |  6    | 850     |
|  102 | Keyboard | computer   |  37   | 40      |
|  103 | Mouse    | computer   |  17   | 30      |
|  104 | Pencil   | fourniture |  148  | 2       |
|  105 | Gum      | fourniture |  250  | 1       |
+------+----------+------------+-------+---------+

To know the average of prices by category, it is possible to use a query that will use :

• GROUP BY to group the orders by category
• The AVG() function to calculate the average of the records

 

 
The query will be as follows:

SELECT category, AVG(price)
FROM Orders
GROUP BY category

The result will be as follows:

+------------+------------+
|  category  | AVG(price) |
+------------+------------+
| computer   |  306.66    |
| fourniture |  1.5       |
+------------+------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More