SQL HAVING statement

The HAVING clause in SQL is used to filter the results of a GROUP BY clause. Unlike the WHERE clause, the HAVING clause works with aggregate functions.

The HAVING clause is used in the SELECT statement, and it is usually added after the GROUP BY clause.

Here is the basic syntax:

SELECT column1, column2, ..., aggregate_function(column_name)
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

Example:

Consider a Sales table:

OrderID	Product	Quantity
1	Apples	10
2	Bananas	20
3	Apples	15
4	Oranges	12
5	Apples	20
6	Bananas	10

If you want to find out the total quantity of each product where the total quantity is greater than 20, you can use the HAVING clause as follows:

SELECT Product, SUM(Quantity) as TotalQuantity
FROM Sales 
GROUP BY Product
HAVING TotalQuantity > 20;

The result would be:

Product	TotalQuantity
Apples	45
Bananas	30

This query groups the sales by product, calculates the total quantity for each group, and then only includes the groups where the total quantity is more than 20 in the results.

Note:

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
The HAVING clause can be used without GROUP BY, functioning as a WHERE clause for aggregate functions.

As always, the exact syntax may vary between different SQL dialects, so be sure to consult the documentation for the SQL dialect you're using.

Filtering Grouped Data with HAVING:
- Description: HAVING is used to filter the results of a GROUP BY operation based on specified conditions.
- Code Example:
```
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
```
Using HAVING with Aggregate Functions:
- Description: HAVING is commonly used with aggregate functions (e.g., COUNT, AVG, SUM) to filter grouped data.
- Code Example:
```
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 10;
```
SQL HAVING vs WHERE Clause:
- Description: WHERE is used to filter rows before grouping, and HAVING is used to filter grouped results.
- Code Example (WHERE):
```
SELECT *
FROM Employees
WHERE Salary > 50000;
```
- Code Example (HAVING):
```
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
```
Conditions in HAVING Statement:
- Description: HAVING conditions are applied to the aggregated values in the result set.
- Code Example:
```
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) BETWEEN 50000 AND 80000;
```
HAVING with GROUP BY in SQL:
- Description: HAVING is commonly used in conjunction with GROUP BY for summarizing and filtering grouped data.
- Code Example:
```
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
```

Examples of HAVING Clause in SQL:

Example 1:

SELECT ProductCategory, COUNT(*) AS ProductCount
FROM Products
GROUP BY ProductCategory
HAVING COUNT(*) > 5;

Example 2:

SELECT OrderID, COUNT(*) AS ItemCount
FROM OrderItems
GROUP BY OrderID
HAVING COUNT(*) >= 3;

HAVING vs ORDER BY in SQL:
- Description: HAVING filters grouped results, while ORDER BY sorts the result set.
- Code Example (ORDER BY):
```
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
ORDER BY AvgSalary DESC;
```

Combining HAVING and GROUP BY with JOIN:

Description: Joins multiple tables, groups the results, and applies conditions using HAVING.

Code Example:

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
GROUP BY Department
HAVING AVG(Salary) > 50000;