Node.js MySQL AVG()

In this post, we will discuss how to perform AVG aggregate function on MySQL table in XAMPP server using Node.js.

It is important to install mysql package in node.js.

Command to install the mysql package:

CopiedCopy Code
npm install mysql
				

AVG() Function

AVG is an aggregate function in sql which is used to return the average in a column. It can also be used with GROUP BY to return the average of values in each group.

Steps for Node.js script:

Now let's see steps

  1. First start your XAMPP Server (Both Apache and MySQL).
  2. Open Notepad or any text-editor and write the Node.js script
  3. In that script, first we have to load the mysql package using the below syntax
  4. var mysql_package = require('mysql');
  5. Create the connection using the server,username and password.
  6. CopiedCopy Code
    
    var connection_data = mysql_package.createConnection({
      host: "localhost",
      user: "root",
      password: "",
      database:"database_name"
    });
    
  7. Write the sql query that uses AVG aggregate function
  8. CopiedCopy Code
    
    connection_data.connect(function(error) {
    connection_data.query("SELECT AVG(column1), AVG(column2),... 
    from table_name)", function (error, result) {
     
        console.log(result);
      });
    });
    
  9. Now type the following command in your command prompt to run the script.
  10. node file_name.js

Consider the field table with the following records:

alt=

Example 1:- AVG()

Node.js Script to return average of values in area column.

CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the 
//server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {
// Write SQL query to return average of values in 
//area column on field table.
  connection_data.query("SELECT AVG(area) FROM field", 
  function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

CopiedCopy Code

[ RowDataPacket { 'AVG(area)': 296.3636 } ]

So average of values present in area column is 296.3636.

Example 2:- AVG() with GROUP BY()

Node.js Script

  1. To group all values in district column and return average of area in each group.
  2. To group all values in field_type column and return average of area in each group.
CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to return average of values in area 
//column by group on district column.
  connection_data.query("SELECT district,AVG(area) FROM field 
  GROUP BY district", function (error, result) {
	  
  console.log("GROUP BY DISTRICT");
//Display the records one by one
    console.log(result);
  });
  
  // Write SQL query to return average of values in area 
  //column by group on field_type column.
  connection_data.query("SELECT field_type,AVG(area) FROM field 
  GROUP BY field_type", function (error, result) {
	  
  console.log("GROUP BY FIELD TYPE");
//Display the records one by one
    console.log(result);
  });
});

Output:

CopiedCopy Code

GROUP BY DISTRICT
[
  RowDataPacket { district: 'guntur', 'AVG(area)': 105 },
  RowDataPacket { district: 'kadapa', 'AVG(area)': 505 },
  RowDataPacket { district: 'nellore', 'AVG(area)': 60 },
  RowDataPacket { district: 'ongole', 'AVG(area)': 780 },
  RowDataPacket { district: 'visakha', 'AVG(area)': 465 }
]
GROUP BY FIELD TYPE
[
  RowDataPacket { field_type: 'black', 'AVG(area)': 66.6667 },
  RowDataPacket { field_type: 'red', 'AVG(area)': 373.3333 },
  RowDataPacket { field_type: 'sand', 'AVG(area)': 388 }
]
  1. The values are grouped based on the values in district column and corresponding average area is returned for each group.
  2. The values are grouped based on the values in field_type column and corresponding average area is returned for each group.

Example 3:- AVG() with WHERE Clause

Node.js Script to return average area with field_type black.

CopiedCopy Code

// Load the mysql package
var mysql_package = require('mysql');

// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});

connection_data.connect(function(error) {
	
// Write SQL query to return average of values in
// area column where field_type is black.
  connection_data.query("SELECT district,field_type,AVG(area) FROM 
  field where field_type='black' ", function (error, result) {
	  
//Display the records one by one
    console.log(result);
  });
});

Output:

CopiedCopy Code

[
  RowDataPacket {
    district: 'guntur',
    field_type: 'black',
    'AVG(area)': 66.6667
  }
]

So we can see that average area is returned with field_type as black.

Summary

In this post, we seen how to return average of values in a column and how to apply GROUP BY along with AVG aggregate function. You can try in your machine with the help of above examples.