Course of the Month: GROUP BY Extensions in SQL

Do you want to develop your SQL skills? Do you want to get more out of your data and prepare better reports and summaries? The answer is learning to use SQL’s GROUP BY extensions. But how should you go about this? I asked the same question to Agnieszka Kozubek-Krycuń, Vertabelo’s Chief Content Officer. As she explains, the secret is in this November’s free SQL Course of the Month: GROUP BY Extensions in SQL.

What is GROUP BY in SQL?

GROUP BY is a SQL construction that lets you group data and then compute statistics on each group. For example, if you’re running an online store, you can compute the average order value for each customer or how many customers you have in each state with GROUP BY.

GROUP BY is a basic mechanism for creating reports in SQL. If you want to know more, I recommend this article about how the GROUP BY clause works.

Our GROUP BY Extensions in SQL course is about the GROUP BY extensions ROLLUP, CUBE, and GROUPING SETS. These extend the regular GROUP BY clause; they allow you to perform multiple groupings with just one SQL query. And they help you prepare SQL reports with less effort.

Will these extensions really help me with my SQL?

Yes! ROLLUP, CUBE, and GROUPING SETS originate from something called OLAP (Online Analytical Processing). This is a fancy term for doing analytics with data. (A similar term, OLTP (Online Transaction Processing), is used for applications that “do” stuff, like your bank application, an online store, or an online dating website. OLTP applications basically record changes in data: you have made a bank transfer, you have bought something, you liked a person’s profile.) OLAP databases are used by companies to compute statistics and perform aggregations on their data: a bank wants to know how much money comes in and out each day, an online store wants to know its monthly sales, a dating website wants to know the demographics of its users, etc.

GROUP BY extensions are used in databases and data processing technologies that focus on computing statistics and aggregates from data. Nowadays, there are many different technologies that are used for this: relational databases, data warehouses, Big Data tools, etc. If the technology in question uses SQL – and most of them do – there’s a good chance you’ll use GROUP BY extensions in your queries when you’re working with these technologies.

Can you show me an example of a GROUP BY extension?

Of course. Let’s imagine we have a database table called sales. Our table contains monthly sales data for each of the different colors of our product. The table has four columns: year, month, color, and amount.

We’d like to find the total sales for each month and each year and we’d also like to know the total number of products sold. You can do all this with just one query:

SELECT year, month, sum(amount) as amount
FROM sales
GROUP BY ROLLUP(year, month)
ORDER BY year, month;

The result of this query would be something like this:

The result of the query contains total sales for each month of each year, as you’d expect with GROUP BY. It also contains summary rows for each year (2020 and 2019) and the total summary row for the total number of sales. This is what ROLLUP did - it has added a summary row for each year and total summary row.

The other extensions, CUBE and GROUPING SETS, work in a similar way. They add additional groupings to the result.

How long is this SQL course? How many exercises will I do?

There are 63 exercises in the course, which is divided into four parts. Each extension gets its own part, and the final part is a quiz. Completing all the parts should take about 7 hours. Of course, no one can do it all at once. It will probably take a week to complete the course.

Is this free course part of a larger track?

Yes, this course is part of our SQL From A to Z track. This is a complete SQL learning path for beginners. It contains a comprehensive set of courses that allow newcomers to learn all the features used in modern SQL, including those used infrequently. We chose this track to be the SQL track of the season for Fall 2020.

This course is also part of our Advanced SQL track. This track is for users who have solid SQL foundations and would like to deepen their knowledge of modern SQL features.

Who should take this course?

This SQL course is perfect for anyone who does data analysis in SQL, especially date and time analysis. I recommend it to data analysts, sales managers, product managers, and financial managers. Computer science and IT students can take this course to broaden their knowledge of SQL. Programmers, software developers, and software engineers can get acquainted with new features of SQL they didn’t know before. If you’re one of these people, this SQL course will be perfect for you.

Will I receive a certificate to post on my LinkedIn profile?

Yes. After completing the exercises in this course, you’ll receive a PDF certificate that you can publish on your LinkedIn profile. See how easy it is to do by reading our short guide.

Do you remember working on this course?

Our SQL course design process was already established by the time we started working on this course, so there weren’t many surprises. Actually, the first course we prepared was its sister course, GROUP BY Extensions for SQL Server. At that time, we focused on developing courses for the SQL Server dialect of SQL. There aren’t many differences between the SQL Server and SQL standard versions of GROUP BY extensions, so later we adapted the SQL Server course to this SQL standard version, GROUP BY Extensions in SQL.

Free SQL Course for November: GROUP BY Extensions in SQL

And now what you've all been waiting for. We have something special for our users. Throughout November, the course GROUP BY Extensions in SQL is FREE!

You can get full access to this great interactive SQL course without paying anything. Sign up and start today! Learn how to create reports and perform effective data analyses using SQL. Become a SQL expert!