SQL HAVING

SQL HAVING is a powerful technique that enables you to filter data based on the results of an aggregate function. In this tutorial, we will cover everything you need to know about SQL HAVING, including its syntax, functions, and practical examples.

Syntax of SQL HAVING

The syntax of SQL HAVING is as follows:

SELECT column1, column2, ..., function(columnX)
FROM table
GROUP BY column1, column2, ...
HAVING condition;

In this syntax, the HAVING clause is used to filter the results of an aggregate function applied to a grouped set of rows. The GROUP BY clause is used to group the rows based on one or more columns, and the SELECT statement can include functions such as COUNT, SUM, AVG, MIN, and MAX.

Functions in SQL HAVING

There are several functions that can be used with SQL HAVING:

1. COUNT

The COUNT function is used to count the number of rows in each group.

2. SUM

The SUM function is used to calculate the sum of a column for each group.

3. AVG

The AVG function is used to calculate the average of a column for each group.

4. MIN

The MIN function is used to find the minimum value of a column for each group.

5. MAX

The MAX function is used to find the maximum value of a column for each group.

Examples of SQL HAVING

Example 1: Filtering Data

Suppose you have a table called customer_orders with columns customer_name, order_date, and order_total, and you want to find the customers who have placed orders with a total value greater than $1000. Here’s an example:

SELECT customer_name, SUM(order_total)
FROM customer_orders
GROUP BY customer_name
HAVING SUM(order_total) > 1000;

In this example, we used the GROUP BY clause to group the orders by customer name, and the SUM function to calculate the total order value for each customer. We then used the HAVING clause to filter the results to only include customers with a total order value greater than $1000.

Example 2: Filtering Data with AVG

Suppose you have a table called employee_salary with columns employee_name, department, and salary, and you want to find the departments where the average salary is greater than $50000. Here’s an example:

SELECT department, AVG(salary)
FROM employee_salary
GROUP BY department
HAVING AVG(salary) > 50000;

In this example, we used the GROUP BY clause to group the salaries by department, and the AVG function to calculate the average salary for each department. We then used the HAVING clause to filter the results to only include departments where the average salary is greater than $50000.

Conclusion

SQL HAVING is a powerful technique that enables you to filter data based on the results of an aggregate function. This technique is useful when you need to filter data that has been grouped based on one or more columns. In this tutorial, we covered the syntax of SQL HAVING, functions that can be used with it, and practical examples. By mastering SQL HAVING, you can take your SQL querying skills to the next level and become a more efficient data analyst or database developer.