Languages
[Edit]
EN

Node.js - MySQL GROUP BY

0 points
Created by:
Dirask Admin - Marvin
1940

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

Example data used with GROUP BY statement - HeidiSQL
Example data used with GROUP BY statement - HeidiSQL

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');

Node.js - MySQL

Native Advertising
πŸš€
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

β€οΈπŸ’» πŸ™‚

Join