Node.js - MySQL GROUP BY

In this article, we would like to show you how to use MySQL GROUP BY in Node.js.

Note: at the end of this article you can find database preparation SQL queries.

const mysql = require('mysql');

const connection = mysql.createConnection({        // gets connection with database
    host: 'localhost', // '127.0.0.1'
    user: 'root',
    password: '',
    database: 'test',
});

connection.connect(error => {
    if (error) throw error;
    const query ='SELECT COUNT(`id`) AS `number of users`, `country` ' + 
                 'FROM `users` ' +
                 'GROUP BY `country` ' +
                 'ORDER BY COUNT(`id`) DESC;';

    connection.query(query, (error, result) => {   // sends queries
        connection.end();                          // closes connection
        if (error) throw error;
        console.table(result);
    });
});

Result: 

┌─────────┬─────────────────┬───────────┐
│ (index) │ number of users │  country  │
├─────────┼─────────────────┼───────────┤
│    0    │       '3'       │  'Spain'  │
│    1    │       '2'       │ 'Vietnam' │
│    2    │       '1'       │  'Italy'  │
│    3    │       '1'       │ 'Poland'  │
└─────────┴─────────────────┴───────────┘

Database preparation

create_tables.sql file:

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`country` VARCHAR(15) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

insert_data.sql file:

INSERT INTO `users`
	(`name`, `country`)
VALUES
	('Tom', 'Poland'),
	('Chris', 'Spain'),
	('Jack', 'Spain'),
    ('Kim', 'Vietnam'),
    ('Marco', 'Italy'),
	('Kate', 'Spain'),
	('Nam', 'Vietnam');