Node.js - MySQL COUNT()

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

COUNT() Function:

COUNT is an aggregate function in sql which is used to return the total number of rows present in a table. We can specify column name inside count or *.

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 COUNT aggregate function.
Copied

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

Node.js Script to return total number of rows in field table.

// 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 number of rows.
  connection_data.query("SELECT COUNT(*) FROM field", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[ RowDataPacket { 'COUNT(*)': 11 } ]

So, totally there are 11 rows in field table.

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

Node.js Script

1. To group all values in district column and return total rows in each group.
2. To group all values in field_type column and return total rows 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 rows in all district groups.
  connection_data.query("SELECT district,COUNT(*) 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 rows in 
  //all field_type groups.
  connection_data.query("SELECT field_type,COUNT(*) 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', 'COUNT(*)': 4 },
  RowDataPacket { district: 'kadapa', 'COUNT(*)': 2 },
  RowDataPacket { district: 'nellore', 'COUNT(*)': 2 },
  RowDataPacket { district: 'ongole', 'COUNT(*)': 1 },
  RowDataPacket { district: 'visakha', 'COUNT(*)': 2 }
]
GROUP BY FIELD TYPE
[
  RowDataPacket { field_type: 'black', 'COUNT(*)': 3 },
  RowDataPacket { field_type: 'red', 'COUNT(*)': 3 },
  RowDataPacket { field_type: 'sand', 'COUNT(*)': 5 }
]

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

Example 3:- COUNT() with WHERE Clause

Node.js Script to return total rows 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 rows where field_type is black.
  connection_data.query("SELECT district,field_type,COUNT(*) 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',
    'COUNT(*)': 3
  }
]

So we can see that there are only 3 rows with field_type as 'black'.

Summary

In this post, we seen how to return total number of rows in a table and how to apply GROUP BY along with COUNT aggregate function. You can try in your machine with the help of above examples.