Node.js MySQL MIN()

In this post, we will discuss how to perform MIN 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:

npm install mysql
				

MIN Function

MIN is an aggregate function in sql which is used to return the minimum value in a column. It can also be used with GROUP BY to return the minimum value 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
var mysql_package = require('mysql');
4. Create the connection using the server,username and password.
Copied

var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"database_name"
});

5. Write the sql query that uses MIN aggregate function.
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT MIN(column1), MIN(column2),... 
from table_name)",function (error, result) {
    console.log(result);
  });
});

6. Now type the following command in your command prompt to run the script.
node file_name.js

Consider the field table with the following records:

Example 1:- MIN()

Node.js Script to return minimum value in area column.

// 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 minimum value from the area column on field table.
  connection_data.query("SELECT MIN(area) FROM field", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[ RowDataPacket { 'MIN(area)': 20 } ]

So, the minimum value present in the area column is 20.

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

Node.js Script

1. To group all values in district column and return minimum area in each group.
2. To group all values in field_type column and return minimum area in each group.

// 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 minimum value in 
//area column by group on district column.
  connection_data.query("SELECT district,MIN(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 minimum value in 
  //area column by group on field_type column.
  connection_data.query("SELECT field_type,MIN(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:

GROUP BY DISTRICT
[
  RowDataPacket { district: 'guntur', 'MIN(area)': 40 },
  RowDataPacket { district: 'kadapa', 'MIN(area)': 120 },
  RowDataPacket { district: 'nellore', 'MIN(area)': 20 },
  RowDataPacket { district: 'ongole', 'MIN(area)': 780 },
  RowDataPacket { district: 'visakha', 'MIN(area)': 40 }
]
GROUP BY FIELD TYPE
[
  RowDataPacket { field_type: 'black', 'MIN(area)': 40 },
  RowDataPacket { field_type: 'red', 'MIN(area)': 120 },
  RowDataPacket { field_type: 'sand', 'MIN(area)': 20 }
]

1. The values are grouped based on the values in district column and corresponding minimum area is returned for each group.
2. The values are grouped based on the values in field_type column and corresponding minimum area is returned for each group.

Example 3:- MIN() with WHERE Clause

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

// 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 minimum value in 
//area column where field_type is black.
  connection_data.query("SELECT district,field_type,MIN(area) FROM 
  field where field_type='black' ", function (error, result) {
	  
//Display the records one by one
    console.log(result);
  });
});

Output

[
  RowDataPacket {
    district: 'guntur',
    field_type: 'black',
    'MIN(area)': 40
  }
]

So we can see that minimum value is 40 for field_type black.

Summary

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