# How to filter grouped entries with SQL HAVING

SQL HAVING is a condition that can be applied to already grouped entries. It works with aggregate functions to restrict result sets.

## What is SQL HAVING?

In addition to the familiar `WHERE` clause, there is another condition in [Structured Query Language](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-with-examples/) that is frequently used: SQL `HAVING`. This condition filters data based on specific criteria. It is applied with the [SQL command](https://www.ionos.com/digitalguide/server/configuration/sql-commands/) `SELECT` and the [SQL GROUP BY](https://www.ionos.com/digitalguide/server/configuration/sql-group-by/) statement. While `GROUP BY` groups results, SQL HAVING restricts the result set **using different aggregate functions**. The condition was introduced because `WHERE` cannot interact with aggregate functions such as [SQL AVG()](https://www.ionos.com/digitalguide/server/configuration/sql-avg/), [SQL COUNT()](https://www.ionos.com/digitalguide/server/configuration/sql-count/), `MAX()`, `MIN()` and `SUM()`. SQL `HAVING` is used after the statements `WHERE` (if available) and `GROUP BY`, but before `ORDER BY`.

## Syntax and function

To understand how SQL `HAVING` works and its benefits, it’s worth taking a look at the syntax of the clause:

```sql
SELECT name_of_column(n) 
FROM name_of_table 
WHERE condition 
GROUP BY name_of_column(n) 
HAVING condition 
ORDER BY name_of_column(n);
```

An aggregate function is often applied to one or more columns, followed by specifying the table name for localization. The `WHERE` condition **is optional**. `GROUP BY` combines identical values into groups, which can be further filtered with HAVING and ordered with `ORDER BY`.

## Example of the condition

The easiest way to illustrate SQL `HAVING` is with the help of a simple example. To do this, we create a small table called “Customer list”. This contains the columns “Customer number”, “Name”, “Location” and “Item”:

<table>
  <thead>
    <tr>
      <th>Customer Number</th>
      <th>Name</th>
      <th>Location</th>
      <th>Item</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1427</td>
      <td>Smith</td>
      <td>New York</td>
      <td>13</td>
    </tr>
    <tr>
      <td>1377</td>
      <td>Johnson</td>
      <td>Los Angeles</td>
      <td>9</td>
    </tr>
    <tr>
      <td>1212</td>
      <td>Brown</td>
      <td>Los Angeles</td>
      <td>15</td>
    </tr>
    <tr>
      <td>1431</td>
      <td>Davis</td>
      <td>Chicago</td>
      <td>22</td>
    </tr>
    <tr>
      <td>1118</td>
      <td>Wilson</td>
      <td>New York</td>
      <td>10</td>
    </tr>
  </tbody>
</table>

Now we use SQL `HAVING` to find out how many customers from Los Angeles have ordered ten or more items. To do this, we use the following code and specify the number using an [SQL alias](https://www.ionos.com/digitalguide/server/configuration/sql-aliases/) as “NumberOrders”:

```sql
SELECT Location, COUNT(Location) AS NumberOrders 
FROM CustomerList 
WHERE Location = 'Los Angeles' 
GROUP BY location, article 
HAVING Article > 10;
```

The corresponding output is:

<table>
  <thead>
    <tr>
      <th>Location</th>
      <th>OrderCount</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Los Angeles</td>
      <td>1</td>
    </tr>
  </tbody>
</table>

## The condition combined with INNER JOIN

You can also combine SQL `HAVING` with the keyword `INNER JOIN`. To do this, we create a second table called “Article\_01”, which stores how often and when a particular article was ordered. It contains an order number, a customer number, the quantity, and an order date and looks like this:

<table>
  <thead>
    <tr>
      <th>Order number</th>
      <th>Customer number</th>
      <th>Quantity</th>
      <th>Date</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>00283</td>
      <td>1427</td>
      <td>4</td>
      <td>2024-15-01</td>
    </tr>
    <tr>
      <td>00284</td>
      <td>1211</td>
      <td>7</td>
      <td>2024-19-01</td>
    </tr>
    <tr>
      <td>00285</td>
      <td>1275</td>
      <td>15</td>
      <td>2024-29-01</td>
    </tr>
    <tr>
      <td>00286</td>
      <td>1431</td>
      <td>10</td>
      <td>2024-01-02</td>
    </tr>
    <tr>
      <td>00287</td>
      <td>1427</td>
      <td>9</td>
      <td>2024-05-02</td>
    </tr>
  </tbody>
</table>

Now we can now instruct the system to show us all customers who have placed more than one order. The appropriate code is this one:

```sql
SELECT Customer list.name, COUNT(Article_01.order number) AS NumberOrders 
FROM (Article_01 
INNER JOIN Customer list ON Article_01.customer_number = Customer_01.customer_number) 
GROUP BY Name 
HAVING COUNT(Article_01.OrderNumber) > 1;
```

We get the following ouput:

<table>
  <thead>
    <tr>
      <th>Name</th>
      <th>Order Count</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Smith</td>
      <td>2</td>
    </tr>
  </tbody>
</table>

## Alternatives to SQL HAVING

An alternative to SQL `HAVING` is `WHERE`. However, the two options cannot be used in exactly the same way. `WHERE` is used for individual entries and can also be combined with `DELETE` or `UPDATE` in addition to `SELECT`. `HAVING`, on the other hand, is **only intended for grouped entries** and is only compatible with `SELECT`. `WHERE` is used before `GROUP BY` and `HAVING` after. In addition, only SQL `HAVING` can work with aggregate functions.

Tip A server that’s perfectly tailored to your needs: With [SQL Server Hosting](https://www.ionos.com/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS, you can choose between MSSQL, MySQL, and MariaDB, benefiting from a robust security architecture, outstanding performance, and personalized advice at all times.


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/sql-having/](https://www.ionos.com/digitalguide/server/configuration/sql-having/) for AI/LLM consumption.