Node.js - MySQL GROUP BY

In this post, we will discuss how to group the rows based on column in MySQL XAMPP Server with Node.js Application.

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

Command to install the mysql package:

npm install mysql
				

GROUP BY Function:

GROUP BY is used to group all the similar values in a column. With this we can perform different aggregate operations like sum,min,max,count and avg.

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 GROUP BY function.
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT columns from table_name 
GROUP BY(column_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:- GROUP BY()

1. Group rows to count based on the values in the area column.
2. Group rows to count based on the values in the district 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 number of rows by 
//grouping values in field_type column.
  connection_data.query("SELECT field_type,COUNT(*) 
  FROM field GROUP BY field_type", function (error, result) {
	  
 console.log("GROUP ROWS BASED ON field_type Column");
//Display the records one by one
    console.log(result);
  });
  
  // Write SQL query to return number of rows by
  // grouping values in district column.
  connection_data.query("SELECT district,COUNT(*)
   FROM field GROUP BY district", function (error, result) {
 
  console.log("GROUP ROWS BASED ON district Column");

//Display the records one by one
    console.log(result);
  });
});

Output:

GROUP ROWS BASED ON field_type Column
[
  RowDataPacket { field_type: 'black', 'COUNT(*)': 3 },
  RowDataPacket { field_type: 'red', 'COUNT(*)': 3 },
  RowDataPacket { field_type: 'sand', 'COUNT(*)': 5 }
]
GROUP ROWS BASED ON district Column
[
  RowDataPacket { district: 'guntur', 'COUNT(*)': 4 },
  RowDataPacket { district: 'kadapa', 'COUNT(*)': 2 },
  RowDataPacket { district: 'nellore', 'COUNT(*)': 2 },
  RowDataPacket { district: 'ongole', 'COUNT(*)': 1 },
  RowDataPacket { district: 'visakha', 'COUNT(*)': 2 }
]

So, we can see that total number of records for each group is returned.

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

1. Group rows in district column and return total area for each group.
2. Group rows in district column and return average for 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 total area for 
//each district group.
  connection_data.query("SELECT district,SUM(area) 
  FROM field GROUP BY district", function (error, result) {
	  
 console.log("Total area in each district");
//Display the records one by one
    console.log(result);
  });
  
// Write SQL query to return average area for 
//each district group.  
  connection_data.query("SELECT district,AVG(area) 
  FROM field GROUP BY district", function (error, result) {
 
 console.log("Average area in each district");

//Display the records one by one
    console.log(result);
  });
});

Output:

Total area in each district
[
  RowDataPacket { district: 'guntur', 'SUM(area)': 420 },
  RowDataPacket { district: 'kadapa', 'SUM(area)': 1010 },
  RowDataPacket { district: 'nellore', 'SUM(area)': 120 },
  RowDataPacket { district: 'ongole', 'SUM(area)': 780 },
  RowDataPacket { district: 'visakha', 'SUM(area)': 930 }
]
Average area in each 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 }
]

So, we can see that sum and average of area for each district groups are returned.

Example 3:- GROUP BY() with MIN(),MAX()

1. Group rows in district column and return minimum value in each group.
2. Group rows in district column and return maximum value 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 
//area for each district group.
  connection_data.query("SELECT district,MIN(area) 
  FROM field GROUP BY district", function (error, result) {
	  
 console.log("Minimum area in each district");
//Display the records one by one
    console.log(result);
  });
  
// Write SQL query to return maximum 
//area for each district group.  
  connection_data.query("SELECT district,MAX(area) 
  FROM field GROUP BY district", function (error, result) {
 
 console.log("Maximum area in each district");

//Display the records one by one
    console.log(result);
  });
});

Output:

Minimum area in each 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 }
]
Maximum area in each district
[
  RowDataPacket { district: 'guntur', 'MAX(area)': 220 },
  RowDataPacket { district: 'kadapa', 'MAX(area)': 890 },
  RowDataPacket { district: 'nellore', 'MAX(area)': 100 },
  RowDataPacket { district: 'ongole', 'MAX(area)': 780 },
  RowDataPacket { district: 'visakha', 'MAX(area)': 890 }
]

So, we can see that minimum and maximum area for each district groups is returned.

Summary

In this post, we seen how to group the rows in a column using GROUP BY with five aggregate functions.