HAVING - (MySQL Clauses)

In MySQL, HAVING is a clause that is used in conjunction with the GROUP BY clause to filter the results of a query based on aggregate functions. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the HAVING clause in MySQL.

Syntax

The syntax for the HAVING clause in MySQL is as follows:

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);

Example

Let's say we have a table called sales, which contains data about different sales transactions. We want to find the salespeople who had a total sales amount greater than $10,000. Here's an example query that uses the HAVING clause:

SELECT salesperson, SUM(amount) as total_sales 
FROM sales 
GROUP BY salesperson 
HAVING total_sales > 10000;

This query will return a table with two columns - the salesperson's name and their total sales amount. The HAVING clause is used to filter the results of the GROUP BY clause, so only the salespeople with a total sales amount greater than $10,000 will be included in the output.

Output

When we run the example query above, we might get the following output:

+------------+-------------+
| salesperson| total_sales |
+------------+-------------+
| John       | 15000       |
| Mary       | 12000       |
| Jane       | 12500       |
+------------+-------------+

This result shows the salespeople who had a total sales amount greater than $10,000.

Explanation

In the example query above, we used the SUM aggregate function to calculate the total sales amount for each salesperson. We then grouped the results by salesperson using the GROUP BY clause. The HAVING clause is then used to filter the results by specifying that only the salespeople with a total sales amount greater than $10,000 should be included in the output.

Use

The HAVING clause is typically used in queries that involve aggregate functions (SUM, AVG, MIN, MAX, COUNT, etc.) and GROUP BY clauses. It is useful for filtering the results of the query based on the results of aggregate functions.

Important Points

• The HAVING clause is used to filter the output of a query based on aggregate functions.
• The HAVING clause is only used with the GROUP BY clause.
• The HAVING clause is similar to the WHERE clause, but the HAVING clause operates on the results of aggregate functions while the WHERE clause operates on individual rows.
• The HAVING clause must come after the GROUP BY clause.

Summary

In this tutorial, we discussed the HAVING clause in MySQL. We covered the syntax, examples, output, explanation, use, and important points of the HAVING clause. By understanding the HAVING clause, you can use it to filter the results of queries according to results of aggregate functions.