How to Use GROUP BY and HAVING in SQL?

The GROUP BY and HAVING clauses in SQL are two of the most powerful tools available for summarising and analysing data. These clauses enable developers and analysts to efficiently group data, perform aggregate calculations, and filter results based on specific criteria. Whether we’re calculating sales performance, monitoring trends, or generating complex reports.

Mastering these SQL concepts is important for unlocking valuable insights hidden in our data. In this article, we will explain the GROUP BY and HAVING clauses with detailed examples, practical use cases, and pro tips to help us optimise our queries and elevate our SQL skills.

Introduction to SQL GROUP BY

The GROUP BY clause in SQL is used to group rows with identical values in specified columns into summary rows, such as calculating the number of customers per country. It is often used with aggregate functions like COUNT(), SUM(), AVG(), etc., to perform calculations on each group of data.

For example, we can use GROUP BY to determine the total sales per product, average scores per class, or the number of users from each region. By grouping data, we can efficiently segment and summarise large datasets, uncovering patterns and trends that might otherwise remain hidden.

Introduction to SQL HAVING

The HAVING clause in SQL is used to filter groups created by the GROUP BY clause. Unlike the WHERE clause, which filters rows before grouping, HAVING is applied after the data is grouped and aggregate calculations are performed. It acts as a filter for the aggregated data, similar to the WHERE clause but is applied to groups rather than individual rows.

This makes HAVING an essential tool for scenarios where we need to focus only on groups that meet specific criteria, such as filtering products with sales above a certain threshold or identifying regions with revenue exceeding a set amount.

Example Table

To illustrate the functionality of GROUP BY and HAVING, let’s consider the following Sales table with sample data. This table contains information about products sold, their quantities, prices, and the countries where the sales occurred.

Sales Table

Examples of SQL GROUP BY

Example 1: Calculate the total quantity sold for each product.

This query groups data by the Product column and calculates sum of Quantity for each product group. The SUM() function is applied to the grouped data, returning a summary for each product.

Query:

SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Product;

Output

Example 1 SQL GROUP BY

Example 2: Find the total revenue generated from each country.

Here, the query groups data by the Country column. The SUM() function calculates the total revenue for each country by multiplying Quantity and Price for each row and summing the results for each group.

Query:

SELECT Country, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Country;

Output

Example 2 SQL GROUP BY

Examples of SQL HAVING

Example 1: Find products with total sales quantity greater than 5.

The query first groups data by Product and calculates the total quantity sold using SUM(). The HAVING clause filters the groups to include only those with SUM(Quantity) greater than 5.

Query:

SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Product
HAVING SUM(Quantity) > 5;

Output

Example 2: Get the countries with total revenue greater than $2000.

The query groups data by Country and calculates total revenue using SUM(Quantity * Price). The HAVING clause filters the results to include only countries where total revenue exceeds $2,000.

Query:

SELECT Country, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Country
HAVING SUM(Quantity * Price) > 2000;

Output

SQL Query Order of Execution

Understanding the order in which SQL processes queries can help you write efficient and accurate statements. The typical execution order is as follows:

1. FROM: Specifies the tables to retrieve data from.
2. WHERE: Filters rows before grouping.
3. GROUP BY: Groups rows with the same values in specified columns.
4. HAVING: Filters groups based on aggregate criteria.
5. SELECT: Specifies the columns to retrieve.
6. ORDER BY: Orders the result set by specified columns.

Conclusion

The GROUP BY and HAVING clauses are indispensable components of SQL, empowering us to summarise, analyse, and filter data effectively. By grouping rows and applying conditions to aggregated data, these clauses allow us to uncover valuable insights and address complex analytical requirements with ease. Mastering these SQL tools will not only enhance our ability to handle large datasets but also position us as a proficient data analyst or developer.

FAQs

What is the difference between WHERE and HAVING in SQL?

The `WHERE` clause filters rows before the grouping occurs, while the `HAVING` clause filters groups after the `GROUP BY` operation.

Can we use the HAVING clause without GROUP BY?

Yes, the HAVING clause can be used without GROUP BY if an aggregate function is present, but it typically follows GROUP BY to filter grouped results.

How does the `GROUP BY` clause interact with aggregate functions?

The GROUP BY clause groups rows with the same values in specified columns, and aggregate functions like SUM(), COUNT(), AVG(), etc., are used to perform calculations on these groups.

S

sudiptadandapat

Follow

Improve

Article Tags :

• Databases
• DBMS
• SQL
• DBMS-SQL
• SQL-Clauses

+1 More