> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# HAVING Clause in SQL

The `HAVING` clause in SQL is used to filter groups of rows based on specified conditions. It is often used with the `GROUP BY` clause to filter groups after aggregate functions have been applied. Unlike the `WHERE` clause, which filters rows before grouping, the `HAVING` clause filters groups after grouping and aggregation.

## Key Points

1. **Filtering Groups**: `HAVING` is used to filter groups of rows based on conditions.
2. **Use with GROUP BY**: It is typically used with the `GROUP BY` clause to filter groups after aggregation.
3. **Aggregate Functions**: Conditions in the `HAVING` clause often involve aggregate functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.
4. **Order of Execution**: `HAVING` is executed after the `GROUP BY` clause and before the `ORDER BY` clause.

## Syntax

```sql theme={"system"}
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
```

* `column1`: The column(s) by which to group the rows.
* `aggregate_function(column2)`: The aggregate function to apply to each group.
* `table_name`: The table from which to retrieve data.
* `condition`: The condition to filter groups.

## Examples

### HAVING with COUNT

Suppose you have a table named `Employees` that stores employee details.

Table: Employees

| EmployeeID | Name    | City       | State      | DepartmentID |
| ---------- | ------- | ---------- | ---------- | ------------ |
| 1          | Anand   | Chennai    | Tamil Nadu | 101          |
| 2          | Bala    | Coimbatore | Tamil Nadu | 102          |
| 3          | Kavitha | Karaikal   | Puducherry | 103          |
| 4          | Raj     | Madurai    | Tamil Nadu | 101          |
| 5          | Kumar   | Trichy     | Tamil Nadu | 102          |

To find departments with more than 1 employee:

```sql theme={"system"}
SELECT DepartmentID, COUNT(EmployeeID) AS NumberOfEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(EmployeeID) > 1;
```

| DepartmentID | NumberOfEmployees |
| ------------ | ----------------- |
| 101          | 2                 |
| 102          | 2                 |

### HAVING with SUM

Suppose you have a table named `Sales` that stores sales data.

Table: Sales

| SaleID | Salesperson | City       | State      | Amount |
| ------ | ----------- | ---------- | ---------- | ------ |
| 1      | Ram         | Chennai    | Tamil Nadu | 5000   |
| 2      | Karthik     | Coimbatore | Tamil Nadu | 6000   |
| 3      | David       | Bangalore  | Karnataka  | 5500   |
| 4      | Kannan      | Chennai    | Tamil Nadu | 7000   |
| 5      | Siva        | Madurai    | Tamil Nadu | 4500   |

To find cities with total sales greater than ₹10,000:

```sql theme={"system"}
SELECT City, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY City
HAVING SUM(Amount) > 10000;
```

| City    | TotalSales |
| ------- | ---------- |
| Chennai | 12000      |

### HAVING with AVG

Suppose you have a table named `Orders` that stores order details.

Table: Orders

| OrderID | CustomerName | City       | State      | Amount |
| ------- | ------------ | ---------- | ---------- | ------ |
| 101     | Ram          | Chennai    | Tamil Nadu | 5000   |
| 102     | Karthik      | Coimbatore | Tamil Nadu | 6000   |
| 103     | David        | Bangalore  | Karnataka  | 5500   |
| 104     | Kannan       | Chennai    | Tamil Nadu | 7000   |
| 105     | Siva         | Madurai    | Tamil Nadu | 4500   |

To find states with an average order amount greater than ₹5,000:

```sql theme={"system"}
SELECT State, AVG(Amount) AS AverageOrderAmount
FROM Orders
GROUP BY State
HAVING AVG(Amount) > 5000;
```

| State      | AverageOrderAmount |
| ---------- | ------------------ |
| Tamil Nadu | 5625               |

## Practical Use Case

Suppose you have a table named `Students` that stores student details.

Table: Students

| StudentID | Name    | City       | State      | Percentage |
| --------- | ------- | ---------- | ---------- | ---------- |
| 1         | Ram     | Chennai    | Tamil Nadu | 92         |
| 2         | Karthik | Coimbatore | Tamil Nadu | 88         |
| 3         | David   | Bangalore  | Karnataka  | 95         |
| 4         | Kannan  | Karaikal   | Puducherry | 91         |
| 5         | Siva    | Madurai    | Tamil Nadu | 89         |

To find states with an average percentage greater than 90:

```sql theme={"system"}
SELECT State, AVG(Percentage) AS AveragePercentage
FROM Students
GROUP BY State
HAVING AVG(Percentage) > 90;
```

| State      | AveragePercentage |
| ---------- | ----------------- |
| Karnataka  | 95                |
| Puducherry | 91                |

## Key Takeaways

1. The `HAVING` clause is used to filter groups of rows based on specified conditions.
2. It is typically used with the `GROUP BY` clause to filter groups after aggregation.
3. Conditions in the `HAVING` clause often involve aggregate functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.
4. `HAVING` is executed after the `GROUP BY` clause and before the `ORDER BY` clause.
5. Use `HAVING` to filter groups and generate meaningful summaries and reports.