How to Use GROUP BY and HAVING in SQL (Beginner-Friendly Guide)

SQL GROUP BY and HAVING help in filtering the datasets. Today, analysts use these widely for various types of analysis . However, they can often be confusing too. Beginners often gent confused and wonder which one to use in which scenario. Let’s understand this in detail.

As we know, SQL is a powerful language for communicating with databases and analyzing records. Aggregation is an important concept in SQL, which helps us in summarizing data. SQL GROUP BY and HAVING help us in this aspect.

For example , If you we want to find the total sales per store, or the average salary per department, or the number of students in each grade, then mastering SQL GROUP BY and HAVING can be extremely useful.

In this article, we will explain with examples what SQL GROUP BY and HAVING do, how they execute, and provide easy-to-understand real world scenarios so you can become the champion of these SQL features quickly.

📌 What is GROUP BY in SQL?

We use GROUP BY clause in SQL to combine rows into groups based on one or more columns. Generally, we use it with aggregate functions as below –

• COUNT() – For counting the number of rows in each group

• SUM() – For getting the total sum of values in each group

• AVG() – Used for finding the average value in each group

• MAX() – To get the highest value in each group

• MIN() –  To get the lowest value in each group

We can use these functions with GROUP BY to summarize data for reporting and analysis. Therefore, analysts use these aggregate functions are frequently. Additionally, we will provide practical examples on these in the article.

GROUP BY Syntax –

Let’s look at below construct to understand the basic syntax of GROUP BY –

🏆 GROUP BY Example 1: Counting Orders per Customer

Let’s understand the above syntax with the help of examples. Suppose we have a table named Orders. We have 3 customers – Alice, Bob and Charlie who placed one or more orders of different amounts.

Now, we want to calculate how many orders each customer has placed. Like, Alice and Bob placed 2 orders and Charlie placed 1.

✅ SQL Query Using GROUP BY

We can write following query using the syntax provided above –

🎯 Output:

Explanation:

• The COUNT(OrderID) function counted the number of orders per customer.

• The GROUP BY CustomerName ensured that we count orders separately for each customer.

💰 GROUP BY Example 2: Total Sales Per Customer

Now, let’s suppose that we want to calculate the total amount spent by each customer.

✅ SQL Query Using SUM()

Below query, in the same syntax is using SUM function to thereby calculate total amount spent by each customer –

🎯 Output:

Explanation:

• Thus, all the values in the Amount column were added up for each customer by SUM(Amount).

🚨 What is the HAVING Clause in SQL?

In SQL GROUP BY and Having, we use the HAVING clause to filter the results of GROUP BY based on conditions we applied to aggregate functions.

Why do we need HAVING?

• First because we can not use the WHERE clause with aggregate functions.

• HAVING is used after GROUP BY is written to filter grouped results.

HAVING Syntax 

Following is the basic syntax for HAVING cllause –

🎯 HAVING Example: Customers Who Spent More Than ₹300

Let’s use the previous example and retrieve only those customers who spent more than ₹300.

✅ SQL Query Using HAVING 

Using the basic syntax from above, we can write the below query to perform this analysis –

🎯 Output:

Explanation:

• The customers who spent ₹300 or less got filtered out by HAVING SUM(Amount) > 300.

• To be specific, Charlie got excluded since he spent only ₹100.

📌 Difference Between WHERE and HAVING

Example:

In nutshell, WHERE filters data before grouping and Having filters data after grouping. Therefore, try to visualize this with below examples –

📊 Combining WHERE, GROUP BY, and HAVING

We can combine WHERE with SQL GROUP BY and HAVING in the same query!

🎯 Example:

Let’s find out customers who made at least two orders and spent more than ₹300.

🎯 Output:

Explanation:

1. WHERE Amount > 100 → Filtered out orders with Amount ≤ 100 before grouping.

2. GROUP BY CustomerName → Grouped orders by customer.

3. HAVING COUNT(OrderID) >= 2 → Only customers with 2+ orders are included with the help of HAVING here.

4. HAVING SUM(Amount) > 300 → Only the customers who spent more than ₹300 are included in this case.

🔥 Real-Life Use Cases of SQL GROUP BY and HAVING

• E-commerce: As E-commerce sites get lot of website traffic, they deploy data analysis teams to understand customer behavior. Hence, think of use cases like calculating total revenue per product category.
• Banking: Among many inactive and low balance accounts, a manager will straightaway want to find customers with high account balances.
• Healthcare: Counting the number of patients per doctor.
• Social Media: Find users with more than 1000 followers.

🎯 Summary

Evidently, it is very important to champion SQL GROUP BY and HAVING to analyze big datasets effectively. Thus, practice 4-5 times using the queries and scenarios discussed above and see the results yourself.🚀

Hope you found this guide helpful! Let me know if you need further clarification. Happy coding!

Also checkout these articles on SQL –

SQL JOINS Explained with Examples

GROUP BY in Excel