Description

The HAVING clause can be used to filter the groups returned by a GROUP BY clause.

  • It is in conjunction (or used along) with the SELECT statement and GROUP BY clause.
  • It can be used only with a SELECT statement.

Let's look at a SELECT statement that uses a HAVING clause to filter the groups returned by a GROUP BY clause.

Table Data

Before executing any queries, consider the below data on the tables.

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219
6 Christina Aguilera Gran Va, 1 Madrid Spain 28001
7 Hanna Moos Forsterstr. 57 Mannheim Germany 68306
8 Frdrique Citeaux 24, place Klber Strasbourg France 67000
9 Laurence Lebihan 12, rue des Bouchers Marseille France 13008
10 Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen Canada T2F 8M4

SELECT With GROUP BY

The below SELECT statement returns the countries and their customer count, only for the countries that have more than one customer.

  • The count(*) returns the number of rows in each group.
  • The HAVING clause condition filters the groups that have more than one customer.

Run this on IDE

SELECT country, count(*) AS customer_count
FROM customers
GROUP BY country
HAVING customer_count > 1;

After successful execution, the output contains the below data.

country customer_count
France 3
Germany 2
Spain 2

Overall

We now understood how to create a SELECT statement with a HAVING clause.

Related Links