Languages
[Edit]
EN

Node.js - MySQL HAVING clause

0 points
Created by:
Dirask JS Member
3460

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

Example data used with GROUP BY statement - HeidiSQL
Example data used with HAVING clause - 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: 'password',
    database: 'database_name',
});

connection.connect(error => {
    if (error) throw error;
    const query ='SELECT COUNT(`id`) AS `number of users`, `country` ' + 
                 'FROM `users` ' +
                 'GROUP BY `country` ' +
                 'HAVING COUNT("id") >= ?;';
    const minimum = [2];            // Each group will have at least 2 users.

    connection.query(query, minimum, (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' │
└─────────┴─────────────────┴───────────┘

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