Node.js - MySQL GROUP BY

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

    });

});

┌─────────┬─────────────────┬───────────┐

│ (index) │ number of users │  country  │

├─────────┼─────────────────┼───────────┤

│    0    │       '3'       │  'Spain'  │

│    1    │       '2'       │ 'Vietnam' │

│    2    │       '1'       │  'Italy'  │

│    3    │       '1'       │ 'Poland'  │

└─────────┴─────────────────┴───────────┘

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 INTO `users`

    (`name`, `country`)

VALUES

    ('Tom', 'Poland'),

    ('Chris', 'Spain'),

    ('Jack', 'Spain'),

    ('Kim', 'Vietnam'),

    ('Marco', 'Italy'),

    ('Kate', 'Spain'),

    ('Nam', 'Vietnam');