Node.js MySQL SUM

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

SUM() Function

SUM is an aggregate function in sql which is used to return the total sum of values in a column. It can also be used with GROUP BY to return the sum 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
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 SUM aggregate function.
Copied

connection_data.connect(function(error) {
connection_data.query("SELECT SUM(column1), SUM(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:- SUM()

Node.js Script to return total values 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 total sum of values in area column on field table.
  connection_data.query("SELECT SUM(area) FROM field", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[ RowDataPacket { 'SUM(area)': 3260 } ]

So sum of values present in area column is 3260.

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

Node.js Script

1. To group all values in district column and return sum of area in each group.
2. To group all values in field_type column and return sum of 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 total sum of values in 
//area column by group on district column.
  connection_data.query("SELECT district,SUM(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 total sum of values in 
  //area column by group on field_type column.
  connection_data.query("SELECT field_type,SUM(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', '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 }
]
GROUP BY FIELD TYPE
[
  RowDataPacket { field_type: 'black', 'SUM(area)': 200 },
  RowDataPacket { field_type: 'red', 'SUM(area)': 1120 },
  RowDataPacket { field_type: 'sand', 'SUM(area)': 1940 }
]

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

Example 3:- SUM() with WHERE Clause

Node.js Script to return sum of 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 total sum of values in 
//area column where field_type is black.
  connection_data.query("SELECT district,field_type,SUM(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',
    'SUM(area)': 200
  }
]

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

Summary

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