GROUP BY & HAVING Clause in SQL

In SQL, the GROUP BY clause categorizes rows with similar values into groups.

The HAVING clause filters groups based on conditions after GROUP BY forms them. It functions like WHERE but for grouped data.

Here’s the order: WHERE filters rows first, then GROUP BY categorizes them, and finally, HAVING filters the groups.

The GROUP BY statement is used with SELECT. WHERE goes before GROUP BY, and ORDER BY goes after.

HAVING is used because WHERE can’t be used with aggregate functions. Both WHERE and HAVING filter records in SQL queries.

Syntax

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

SQL

Difference between HAVING and WHERE Clause

The following table shows the comparisons between these two clauses. Still, the main difference is that the WHERE clause uses a condition for filtering records before any groupings are made. In contrast, the HAVING clause uses a condition for filtering values from a group.

HAVING	WHERE
1. The HAVING clause is used in database systems to fetch the data/values from the groups according to the given condition.	1. The WHERE clause is used in database systems to fetch the data/values from the tables according to the given condition.
2. The HAVING clause is always executed with the GROUP BY clause.	2. The WHERE clause can be executed without the GROUP BY clause.
3. The HAVING clause can include SQL aggregate functions in a query or statement.	3. We cannot use the SQL aggregate function with WHERE clause in statements.
4. We can only use SELECT statement with HAVING clause for filtering the records.	4. Whereas, we can easily use WHERE clause with UPDATE, DELETE, and SELECT statements.
5. The HAVING clause is used in SQL queries after the GROUP BY clause.	5. The WHERE clause is always used before the GROUP BY clause in SQL queries.
6. We can implement this SQL clause in row operations.	6. We can implements this SQL clause in row operations.
7. It is a post-filter.	7. It is a pre-filter.
8. It is used to filter groups.	8. It is used to filter the single record of the table.

EXAMPLES OF GROUP BY & HAVING Clause in SQL

Example 1: Finding the total number of orders for each customer who has placed more than 3 orders.

order_id	        customer_id
1	                    101
2	                    102
3	                    101
4	                    103
5	                    102
6	                    101
7	                    102

SQL

SELECT customer_id, COUNT(order_id) AS num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 3;

SQL

Output

customer_id	          num_orders
101	                      4
102	                      4

SQL

Example 2: Calculating the total sales amount for each product category where the average sales amount is greater than $500.

product_category	        sales_amount
Electronics	                 200
Clothing		                 400
Electronics	                 	600
Clothing		                 800
Electronics		                 300
Clothing		                 700
Electronics		                 500

SQL

SELECT product_category, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_category
HAVING AVG(sales_amount) > 500;

SQL

Output

product_category	     total_sales
Clothing	               1900

SQL

Example 3: Identifying the departments with more than 3 employees and their average salary is above $5000.

department	             salary
IT	                      6000
HR	                       5500
IT	                       6500
HR	                       5000
Sales	                     7000

SQL

Output

department	            avg_salary
IT	                      6100

SQL

Conclusion

In conclusion, the GROUP BY and HAVING clauses in SQL are essential tools for data aggregation, grouping, and filtering. They enable users to perform advanced analyses and extract meaningful insights from large datasets.

The GROUP BY clause allows for the categorization of rows into groups based on common values in specified columns. This facilitates the computation of aggregate functions, such as SUM(), AVG(), COUNT(), etc., on each group separately. It’s particularly useful for tasks like summarizing data, and calculating totals, averages, or counts within distinct groups.

On the other hand, the HAVING clause filters the grouped data based on conditions applied to the results of aggregate functions. It allows users to impose conditions on the grouped data, enabling the extraction of specific groups that meet defined criteria.

Together, these clauses empower SQL users to perform complex data manipulations, generate insightful reports, and derive actionable conclusions from diverse datasets. Whether it’s analyzing sales figures, employee performance, or customer behavior, the GROUP BY and HAVING clauses play a crucial role in facilitating data-driven decision-making and enhancing the effectiveness of SQL queries.

Frequently Asked Questions

Can I use aggregate functions without GROUP BY?

Yes, you can use aggregate functions without GROUP BY to compute summary statistics across the entire dataset. However, using GROUP BY allows you to compute aggregates on subsets of data, providing more detailed insights.

Can I use HAVING without GROUP BY?

In most cases, when used with GROUP BY, HAVING filters grouped data based on aggregate function results. However, some databases technically allow using HAVING without GROUP BY, but it’s less common and may lack semantic meaning in most scenarios.