GROUP BY, HAVING, and WITH ROLLUP - A Colorful Guide to Learning SQL with Tables!

In this article, you'll learn how to use GROUP BY to organize your data, how to combine it with aggregate functions like SUM, COUNT, and AVG, and how to enhance your queries using HAVING and WITH ROLLUP. With real-life examples and clear explanations, you'll be able to write more efficient and insightful SQL queries in no time.

What is GROUP BY?

One of the most commonly used functions when working with data in the SQL is the GROUP BY function. GROUP BY is used to group data based on a specific column. It is especially useful when combined with aggregate functions such as SUM, MAX, MIN, and AVG. It supports to analyze large datasets efficiently.

Example

Let’s assume we have data related to a store’s products. In this dataset, there is a column called “Main Category” which categorizes products into different types such as electronics, furniture, and personal care. The GROUP BY function allows us to group data based on these categories. So, how does it work?

Analyze with Colorful Tables

Table 1. Product list with prices and categories.

Table 1 shows data about the products sold by a created store. I have highlighted different categories in the “Main Category” column with different colors to make it easier to understand.

Now, I want to find out the total number of products in each category and the total price of the products in each category. I can easily do this using the GROUP BY function.

SELECT main_category, 
    COUNT(*) AS product_count, 
    SUM(product_price) AS total_price
FROM store_data
GROUP BY main_category;

In the SQL query above, the data from the “store_data” table is grouped based on the “main_category” column. The query retrieves the Main Category, the number of products in each category and the total price of the products in that category. The result of the query is shown in Table 2.

Table 2. The result of the query using GROUP BY.

Still not enough? Let’s make it even more meaningful!

In our example, we grouped the products and obtained desired result with GROUP BY. However, there might still be some confusion about how the grouping is actually done.

Let’s imagine that while using GROUP BY, a temporary table is created for each category to better understand the concept.

If we visualize the GROUP BY function as creating tables, it might be easier to meaning as shown in Figure 1. However, keep in mind that no such actual table formation occurs.

Figure 1. Tables of different products.

Understood, but is that really all? Let’s dive deeper!

The GROUP BY clause can also be used with various SQL functions. One of these is the HAVING function, which is similar to WHERE in functionality.

What is HAVING and How is it Used?

HAVING provides filtering after the grouping process in SQL.

Example:

Let’s say we want to see only the categories that have at least 2 products in the grouped store data. In this case, we use the HAVING clause. Below is the query written for this example, and the result of the query is shown in Table 3. This way, we can apply filtering to the groups.

SELECT main_category, 
    COUNT(*) AS product_count, 
    SUM(product_price) AS total_price
FROM store_data
GROUP BY main_category
HAVING COUNT(*) >= 2;

Table 3. The result of the HAVING function.

If we think of this filtering process as being applied after the results of Table 2, it becomes easier to understand.

Using WITH ROLLUP in SQL

Example:

If we want to find out the total number of products and the total price across all categories in the Table 2 grouping by the main category, we can easily achieve this using WITH ROLLUP. Below is the example query, and the result is shown in Table 4.

Finally, let’s discuss the WITH ROLLUP feature used with the GROUP BY function. This feature allows the total value of each grouped column to be displayed in the last row.

SELECT main_category, 
    COUNT(*) AS product_count, 
    SUM(product_price) AS total_price
FROM store_data
GROUP BY main_category WITH ROLLUP;

Table 4. The result of using WITH ROLLUP.

As shown in the results, the total values for the columns have been placed in the last row. However, since the category names in the first column are strings, the total row displays a NULL value for the category name.

Conclusion

In this article, we learned how to use GROUP BY, HAVING, and WITH ROLLUP in SQL. Now, you know how to group data, apply filters, and add total values in SQL!

How do you use these functions in your SQL queries? I hope, you can share your thoughts in the comments! 😊

If you enjoyed this content, feel free to follow me and share this article to help more people learn. Thanks for your support! 🙌