Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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: 19-Nov-2020
487 Views
Advertisements