SQL - Select Having

The HAVING clause in SQL is used to filter the results of a GROUP BY query based on a condition applied to the grouped data. It allows you to specify conditions on aggregated values, such as the result of an aggregate function, and retrieve only those groups that satisfy the specified conditions. The HAVING clause is similar to the WHERE clause but is specifically used with grouped data.

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

Example:

Let's assume we have a "Sales" table with the following columns:

ProductID (unique identifier for each product)

CategoryID (category identifier for the product)

Quantity (quantity sold)

Price (price per unit)

To retrieve the categories along with the total quantity sold for categories that have a total quantity greater than 100, we can use the GROUP BY clause with the HAVING clause:

SELECT CategoryID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY CategoryID
HAVING SUM(Quantity) > 100;

Explanation:

The SELECT statement specifies the columns we want to retrieve from the table. In this case, we select CategoryID and use the SUM aggregate function to calculate the total quantity sold for each category.

The FROM clause indicates the table from which we want to retrieve data. In this example, it's the "Sales" table.

The GROUP BY clause is used to group the rows based on the CategoryID column. This means that the SUM function will be applied to each group separately.

The HAVING clause is used to specify the condition that must be met by the grouped data. In this case, we use SUM(Quantity) > 100 to filter out groups where the total quantity is not greater than 100.

By executing this query, you will retrieve the CategoryID and the total quantity sold for each category that has a total quantity greater than 100.