Syntax error How to calculate an average value across database rows in MySQL?

How to calculate an average value across database rows in MySQL?

For this, you can use AVG(). Following is the syntax −

select avg(yourColumnName1) as anyAliasName1,
avg(yourColumnName2) as anyAliasName2,
avg(yourColumnName3) as anyAliasName3,
.
.
N
from yourTableName;

Let us create a table −

mysql> create table demo31
−> (
−> value1 int,
−> value2 int,
−> value3 int
−> );
Query OK, 0 rows affected (2.27 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo31 values(34,55,67);
Query OK, 1 row affected (0.27 sec)

mysql> insert into demo31 values(50,60,70);
Query OK, 1 row affected (0.16 sec)

mysql> insert into demo31 values(100,200,300);
Query OK, 1 row affected (0.14 sec)

mysql> insert into demo31 values(10,300,200);
Query OK, 1 row affected (0.15 sec)

Display records from the table using select statement −

mysql> select *from demo31;

This will produce the following output −

+--------+--------+--------+
| value1 | value2 | value3 |
+--------+--------+--------+
|     34 |     55 |     67 |
|     50 |     60 |     70 |
|    100 |    200 |    300 |
|     10 |    300 |    200 |
+--------+--------+--------+
4 rows in set (0.00 sec)

Following is the query to calculate an average value across database rows −

mysql> select avg(value1) as Value1_AVG,
−> avg(value2) as Value2_AVG,
−> avg(value3) as Value3_AVG
−> from demo31;

This will produce the following output −

+------------+------------+------------+
| Value1_AVG | Value2_AVG | Value3_AVG |
+------------+------------+------------+
| 48.5000    | 153.7500   | 159.2500   |
+------------+------------+------------+
1 row in set (0.00 sec)
Updated on: 2020-11-19T12:31:38+05:30

676 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements