Languages
[Edit]
EN

Node.js - MySQL HAVING clause

0 points
Created by:
Lillie-Rose-Finnegan
309

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