Skip to main content

GROUP BY and HAVING

Premium

The GROUP BY clause is used to group rows in a table by one or more columns, and the HAVING clause is used to filter the grouped data based on aggregate functions such as SUM, COUNT, AVG, and others.

Basic Syntax of GROUP BY

Here's the overall structure of a SQL query that contains a GROUP BY clause. Notice that it goes after the WHERE condition but before the ORDER BY clause. The HAVING clause, if used, directly follows the GROUP BY clause.

SQL
SELECT column_names
FROM table_name
WHERE condition
GROUP BY column_names
HAVING condition
ORDER BY column_names;

Using GROUP BY in practice

Here's an example that demonstrates the use of GROUP BY and HAVING. Suppose we have a sales table that contains information about sales made by different sales representatives. We want to see the total sales made by each sales representative, and only see those sales representatives who have made more than $10,000 in sales.

SQL
SELECT sales_rep, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY sales_rep
HAVING SUM(sales_amount) > 10000;

In this example, the GROUP BY clause groups the rows in the sales table by the sales_rep column. The HAVING clause filters the grouped data based on the aggregate function SUM, which calculates the total sales made by each sales representative. The result of this query is a table that shows the total sales made by each sales representative who has made more than $10,000 in sales.

Another example of using GROUP BY and HAVING is to calculate the average rating for different genres of movies in a movies table. Suppose we have a movies table with columns for title, genre, and rating. We want to see the average rating for each genre of movie.

SQL
SELECT genre, AVG(rating) AS avg_rating
FROM movies
GROUP BY genre;

In this example, the GROUP BY clause groups the rows in the movies table by the genre column. The AVG aggregate function calculates the average rating for each genre. The result of this query is a table that shows the average rating for each genre of movie.

GROUP BY and HAVING are powerful clauses that allow you to group and aggregate data in meaningful ways. In the next lessons in this module, we will learn more about specific aggregate functions and how to use them in SQL.

What’s the difference between WHERE and HAVING?

The HAVING clause and WHERE clause are two important clauses in SQL that are used to filter results from a query, and at first they may seem hard to distinguish. The main difference between the two is in their scope and the order in which they are applied.

The WHERE clause is used to filter individual rows in a table based on specified conditions. It’s applied before the query aggregates any data and is used to eliminate any rows that do not meet the specified conditions.

The HAVING clause, on the other hand, operates on the results of the query after aggregation has been performed. It is used to filter results based on conditions that involve aggregate functions such as SUM, AVG, COUNT, etc. The HAVING clause is used to filter results based on conditions that cannot be specified in the WHERE clause, such as conditions that use aggregate functions.

Next Lesson