Back to articles list August 20, 2020 - 9 minutes read 5 Examples of GROUP BY Adrian Więch Adrian is an active software developer focused on Java and SQL-related technologies. He holds a Master’s Degree in both Computer Science and Applied Linguistics. While most people focus on specialization, he goes against the flow and believes that diversity is key to personal development. You’ll find him weightlifting, playing the piano, studying psychology and travelling around the world. Tags: sql learn sql group by When you start learning SQL, you quickly come across the GROUP BY clause. Data grouping—or data aggregation—is an important concept in the world of databases. In this article, we’ll demonstrate how you can use the GROUP BY clause in practice. We’ve gathered five GROUP BY examples, from easier to more complex ones so you can see data grouping in a real-life scenario. As a bonus, you’ll also learn a bit about aggregate functions and the HAVING clause. SQL is a universal language to talk to databases that has been around for almost 50 years. If you’re a complete beginner, consider taking our SQL Basics course before reading this article. One of the core concepts behind SQL is data grouping, or data aggregation. If you’re reading this article, you’ve probably already heard about the GROUP BY clause. To help you understand it better, we’ve presented five business problems and showed how they can be solved in our GROUP BY examples. If you need a quick introduction to GROUP BY, watch our five-minute YouTube video. In this article, we’re going to help an imaginary museum analyze their guests. We’ll use the history of visits to the museum to derive meaningful insights using the GROUP BY clause. Let’s get down to work, then! Input Data We’re going to work with a single table named visit. Each row represents a single visit to the museum. Below, you can see a few sample rows from this table: visit datepriceduration 2020-05-01215 2020-05-01437 2020-05-06724 ... As you can see, the table is not very complicated. It only contains three columns: date—The date of the visit to the museum. price—The price paid for the ticket in dollars (you will see a variety of prices because the museum uses different pricing options on various days along with many types of reduced tickets). duration—The duration of the visit to the museum in minutes. By the way, if you already know basic SQL statements and want to try creating tables instead, take a look at this LearnSQL.com Table Basics course. Why Do We Group Rows? We know that we can aggregate (group) rows in SQL, but why do we do that? The GROUP BY clause is typically used alongside aggregate functions, which compute various statistics about the groups of rows. The five most basic aggregate functions in SQL are: COUNT()—Used to count the number of rows. AVG()—Used to find the average value. MIN() and MAX()—Used to find the minimum and maximum value, respectively. SUM()—Used to find the sum of all values. In short, we group rows to compute various statistics. GROUP BY Examples Good. Now that we know a bit about aggregate functions, let’s take a look at five GROUP BY examples. Example 1: GROUP BY With One Column We’ll start with a simple example. We want to find out how many people visited the museum on each day. In other words, for each date, we’ll show the number of visits to the museum. The query we need will look like this: SELECT date, COUNT(*) FROM visit GROUP BY date; We only have two columns: date and count. COUNT(*) means “count everything.” Because we also use the date column in the GROUP BY clause, we’ll see a separate count for each date. When we run the query in our database, we should see something like this: datecount 2020-06-297 2020-05-236 2020-06-235 ... Excellent. We now know how many people visited the museum on each day. Instead of COUNT(*), which means “count every row,” we could also use a column name inside, e.g., COUNT(duration). The difference is that COUNT(*) counts all rows for a given group even if some rows contain NULL (unknown) values. COUNT(duration), on the other hand, only counts those rows that have a non-NULL value in the duration column. In our table, however, there are no NULL values, so the result would be the same. Example 2: GROUP BY With Two Columns We now want to know the average price paid for a ticket in a given month. For this, we’ll need a more complicated query. Take a look: SELECT EXTRACT(YEAR FROM date) AS year, EXTRACT(MONTH FROM date) AS month, ROUND(AVG(price), 2) AS avg_price FROM visit GROUP BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date); We used the EXTRACT(YEAR FROM date) function to get the year from each date, and we used EXTRACT(MONTH FROM date) to get the month (as a numerical value, where “1” means “January,” “2” means “February,” etc.). Note that they are treated as separate columns, so you’ll see the year in one column and the month in another one. Because we want to see the average for each month of each year separately, we also need to group by these two columns. We have to repeat the same functions in the GROUP BY clause. The third column is a combination of two functions. Inside, we’ve got AVG(price), which will calculate the average price in each group. We also have ROUND(AVG(price), 2)) to round the average value to two decimal places. When you use a GROUP BY clause, try to remember the golden rule: All column names from the SELECT clause should either appear in the GROUP BY clause or be used in the aggregate functions. In this case, both EXTRACT(YEAR FROM date) and EXTRACT(MONTH FROM date) should appear in the GROUP BY clause. If you forget about one of them, you’ll probably see an error. The third column uses an aggregate function, AVG(price), so it’s not mentioned in the GROUP BY clause. There are some exceptions to this rule, and they may lead to unexpected behavior. When we run the query, we’ll see something like this: yearmonthavg_price 202057.52 202066.70 As you can see, the average ticket price decreased in June, compared to May. This could translate into lower income for the museum. Example 3: GROUP BY and ORDER BY This time, we want to find the average visit duration value for each month. We also want to make sure that the rows are sorted chronologically. The query we’ll need will be similar to the previous example: SELECT EXTRACT(YEAR FROM date) AS year, EXTRACT(MONTH FROM date) AS month, ROUND(AVG(duration), 2) FROM visit GROUP BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date) ORDER BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date); The new piece here is the ORDER BY clause. An ORDER BY clause lets us specify the order in which we should see the rows. In this case, we want to see all rows sorted first by the year, and then by the month. Again, we need to repeat the same functions from the SELECT clause for the ORDER BY clause to work. By default, ORDER BY sorts the rows in the ascending order. If you want to see rows sorted in descending order, you need to add the DESC keyword after the column name. For example, write ORDER BY EXTRACT(YEAR FROM date) DESC. You can read more about the difference between GROUP BY and ORDER BY in this article. When we run the query, we’ll see something like this: yearmonthavg_duration 2020547.61 2020651.33 On average, a guest spent more time in the museum in June than in May. That’s good news! Example 4: GROUP BY and HAVING Now, we have the following problem: we want to see the average ticket price for each day. However, there’s an extra condition: we don’t want to show days with 3 or fewer visits. This condition translates into a new piece in our SQL query. Take a look: SELECT date, ROUND(AVG(price), 2) AS avg_price FROM visit GROUP BY date HAVING COUNT(*) > 3 ORDER BY date; The new part here is HAVING COUNT(*) > 3. HAVING is a clause we can use to filter on the grouped rows. In this case, we group rows by the date (GROUP BY date). When we do that, we want to make sure that a given group has more than three rows (HAVING COUNT(*) > 3). If a group (in this case, visits on a given day) doesn’t fulfill this condition, we don’t show it at all. When we run the query, we’ll see something like this: dateavg_price 2020-05-015.80 2020-05-157.00 2020-05-236.67 ... Example 5: GROUP BY, HAVING, and WHERE Finally, we have the following problem to solve: we want to show the average visit duration for each day. Again, we only want to show days with more than three visits. However, we also want to make sure that visits which are five minutes long or shorter are not included in the calculations. These are most probably tests performed by the museum employees, so we want to ignore them. This is the query we’ll need: SELECT date, ROUND(AVG(duration), 2) AS avg_duration FROM visit WHERE duration > 5 GROUP BY date HAVING COUNT(*) > 3 ORDER BY date; The new part here is the WHERE clause. It is used to only include visits that lasted more than five minutes. The WHERE and HAVING clauses may look similar, but there is a difference between them: WHERE is used to filter single rows before they are grouped (i.e., single visits) while HAVING is used to filter groups of rows (i.e., visits on a given day). Read more in this article. When we run the query, we’ll see something like this: dateavg_duration 2020-05-0129.80 2020-05-1555.75 2020-05-2332.17 2020-05-2969.50 2020-06-0239.83 2020-06-0448.67 2020-06-0948.50 2020-06-2351.60 2020-06-2957.86 Note how the average visit duration increases with almost every day in June. It looks like we added aninteresting exhibition and our guests spread the word about it. Summary and Follow-Up With these five examples, we went from easy to more complex GROUP BY cases. The versatility of SQL allowed us to analyze the museum visits and answer several questions about them. This shows how effectively GROUP BY can solve real business problems. If you’d like to read more about the GROUP BY clause, our Chief Content Editor, Agnieszka, prepared a comprehensive article available here. If you feel like learning more SQL, have a look at LearnSQL.com. LearnSQL.com team teaches SQL from scratch in a completely interactive manner. For beginners, we have our best-selling SQL Basics course. We guarantee a smooth entry into the world of coding for people with no prior experience in IT. You won’t need to worry about the technical setup—you will study straight from your web browser. We take care of the database while you focus on the key concepts of SQL. If you want to know more about the benefits of learning with us before you purchase a subscription, take a look at our article: Why Take the SQL Basics Course at LearnSQL.com. This article explains in detail why we built the course, what it contains, and the philosophy behind it. Tags: sql learn sql group by You may also like Using GROUP BY in SQL Master the powerful SQL GROUP BY command. Learn to compute statistical summaries with aggregate functions to solve real-world Business Intelligence challenges. Read more GROUP BY Clause: How Well Do You Know It? The GROUP BY clause is the most basic way to compute statistics in SQL. It can be quite tough for beginners but it is really powerful. Read more Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more SQL Window Functions vs. GROUP BY: What’s the Difference? Window functions and GROUP BY may seem similar at first, but they’re quite different. Learn how window functions differ from GROUP BY and aggregate functions. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Getting the Hang of the GROUP BY Clause GROUP BY is an important part of the SQL SELECT statement. But new SQL coders can run into some problems when this clause is used incorrectly. Here’s how to avoid those issues. Read more What Is the Difference Between a GROUP BY and a PARTITION BY? What is the difference between a GROUP BY and a PARTITION BY in SQL queries? When should you use which? You can find the answers in today's article. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.