Working with Having in MySQL
01.19.2022
Intro
The HAVING clause allows us to filter group rows from SQL. When using the GROUP BY clause, our groups are created from rows already filters, from using the WHERE clause. Thus, we can use the HAVING clause to filter our new group rows. In this article, we will learn how to use HAVING clause in MySQL.
The Syntax
The basic syntax of using a Group By is as follows:
SELECT
[columns]
FROM
[table]
WHERE
[conditions]
GROUP BY
[columns]
HAVING
[conditions];Getting Setup
We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called docker-compose.yml and add the following.
version: '3'
services:
db:
image: mysql:latest
container_name: db
environment:
MYSQL_ROOT_PASSWORD: root_pass
MYSQL_DATABASE: app_db
MYSQL_USER: db_user
MYSQL_PASSWORD: db_user_pass
ports:
- "6033:3306"
volumes:
- dbdata:/var/lib/mysql
phpmyadmin:
image: phpmyadmin/phpmyadmin
container_name: pma
links:
- db
environment:
PMA_HOST: db
PMA_PORT: 3306
PMA_ARBITRARY: 1
restart: always
ports:
- 8081:80
volumes:
dbdata:Next, run docker-compose up.
Now, navigate to http://localhost:8081/ to access phpMyAdmin. Then log in with the username root and pass root_pass.
Click the SQL tab and you are ready to go.
Creating a DB
In this article, we will need some data to work with. If you don't understand these commands, don't worry, we will cover them in later articles.
We will be using the sample db provided here: https://dev.mysql.com/doc/sakila/en/. However, we will only enter what we need rather than import the whole db.
With the SQL tab open (or your own sql cli going), let's first create our DB and select it.
create DATABASE if not EXISTS sakila;
USE sakila;Next, let's create an actor table.
CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;And finally, let's enter a few rows.
INSERT INTO actor VALUES
(1,'PENELOPE','GUINESS','2006-02-15 04:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 04:34:33'),
(3,'ED','CHASE','2006-02-15 04:34:33'),
(4,'JENNIFER','DAVIS','2006-02-15 04:34:33'),
(5,'JOHNNY','LOLLOBRIGIDA','2006-02-15 04:34:33'),
(6,'BETTE','NICHOLSON','2006-02-15 04:34:33'),
(7,'GRACE','MOSTEL','2006-02-15 04:34:33'),
(8,'MATTHEW','JOHANSSON','2006-02-15 04:34:33'),
(9,'JOAN','JOHANSSON','2006-02-15 04:34:33')Having Example
In this example, we create a new count using the COUNT aggregate. We can filter the groups returned by using HAVING. Here, we filter our groups by all groups with more than 1 count. We can see there are two actors with the last name, JOHANSSON.
SELECT
last_name AS LastName,
COUNT(*) AS LastNameCount
FROM
actor
GROUP BY LastName
HAVING LastNameCount > 1;| LastName | LastNameCount |
|---|---|
| JOHANSSON | 2 |