SQL Group By Statements: Easy Examples and a Fun Tutorial


Share this post:
*Much lonely...

Nobody likes to feel lonely. Not even your SQL table data. Fortunately, a SQL GROUP BY clause can bring it all together—so your data never feels alone.

GROUP BY clauses are a powerful way to organize your SQL data into useful statistics and business insights. This fun tutorial will teach you what GROUP BY clauses are—and how to use them—via simple, easy-to-understand examples.

group by anmials
*That's better isn't it!

What’s a SQL GROUP BY Clause?

SQL GROUP BY clauses group together rows of table data that have the same information in a specific column. Let’s say you have a table column “country name” and another column “continent name." If you GROUP BY the “continent name” column, you can distill the table down to a list of individual continent names.

The GROUP BY clause in the SELECT statement indicates which column you want to GROUP BY. After that, the query returns a list of the unique entries for the indicated column. You can SELECT additional columns to display in the query in addition to the column indicated in the GROUP BY clause, but you’ll have to add up (or aggregate) the additional columns with SUM/COUNT commands.

If any of that wasn’t clear, don’t worry. The following examples will help you understand.

How to Use a GROUP BY Clause

It’s easier to understand GROUP BY clauses by visualizing one in action, so let’s expand the previous example table. Imagine a table named “world” with country statistics organized into the following columns: “name,” “continent,” “area,” “population,” and “gdp.”

group-by-image-1.png
A typical table of data

The following examples will show you how to query this table with progressively more complicated SELECT statements.

1. SELECT + GROUP BY

Imagine you want to aggregate or group above table into continent data alone. You’ll write a SELECT statement that GROUPs BY the “continent” column like this:

SELECT continent
  FROM world
    GROUP BY continent;

This query produces the following result:

group-by-image-2.png
Useful, but how about a SUM?

Easy-peasy, right?

2. SELECT + GROUP BY + COUNT/SUM

After GROUPing BY, if you want to know more about these continents, you’ll need to SELECT more columns of data. However, since you have grouped together (or aggregated) a lot of data into each continent row, you’ll need to add up whatever's inside before you can display the information. You’ll do that with SUM and COUNT functions.

If the column is an integer (number) value, you’ll use SUM to add up the information. If the column is an alphabetical value, you’ll use COUNT to add up the information. If you don’t use the SUM/COUNT functions when selecting additional columns, the query will come back with an error.

Here’s what your SELECT statement looks like when SELECTing table columns in addition to the one you GROUPed by. See how we used COUNT for alphabetical columns and SUM for numerical columns?

SELECT continent, COUNT(name), SUM(area), SUM(population), SUM(gdp)
  FROM world
    GROUP BY continent;

This query produces the following result:

group-by-image-3.png
Count & Sum - Two Powerful Tools with Group By Statements

Take a look at the above result and notice the following:

  1. GROUP BY: We grouped the “world” table by the “continent” column.
  2. COUNT: We counted the different country “names” in each continent group and listed the total number.
  3. SUM: We summed the numbers for “area,” “population,” and “gdp” found in each group and listed the total figure.

3. SELECT + GROUP BY + COUNT/SUM + HAVING

We can add another layer of distillation to this query with a HAVING clause. The HAVING clause lets you pull specific information out of the aggregated data. Since this additional refinement happens after GROUPing the data, HAVING clauses always come after the GROUP BY clause.

Here, we add a HAVING clause to pull out data for only continents with total populations over 150 million:

SELECT continent, COUNT(name), SUM(area), SUM(population), SUM(gdp)
  FROM world
    GROUP BY continent
    HAVING SUM(population) > 150000000;

This query produces the following result:

group-by-image-4.png
Having Provides a Useful Tool On Counted or Summed Data

Look at that! Your data is getting cozier, and less lonely by the second!

4. SELECT + GROUP BY + COUNT/SUM + HAVING + ORDER BY

Since we’re focusing on population data, wouldn’t it be nice to ORDER BY the SUM(population)? We can organize the result by population (from least to greatest) by adding an ORDER BY clause to the query.

Check it out:

SELECT continent, COUNT(name), SUM(area), SUM(population), SUM(gdp)
  FROM world
    GROUP BY continent
      HAVING SUM(population) > 150000000
      ORDER BY SUM(population);

Here’s the result:

group-by-image-5.png
Ordering lets you display things in the . . . order you want

*If you want to ORDER BY a descending order, just put DESC after the clause like this: ORDER BY SUM(population) DESC.

5. SELECT + GROUP BY + COUNT/SUM + ORDER BY + WHERE

To keep the above examples simple, we didn’t include a WHERE clause yet, so let's try one now. We'll use a WHERE clause to SELECT specific data from the table. After that, we'll apply the GROUP BY function to the data selected in the WHERE clause.

Let's say that another way: We’re going to isolate specific rows of the table with the WHERE clause, then we're going to group the information with a GROUP BY clause.

The following query lists information for countries that start with the letter A only. Next, it groups the resulting data together by continent. Then, it orders the list, beginning with the continents that have the most “A” countries first. Here’s how that query looks:

SELECT continent, COUNT(name), SUM(area), SUM(population), SUM(gdp)
 FROM world
  WHERE name LIKE 'A%'
    GROUP BY continent
    ORDER BY COUNT(name) DESC;

Can you see what we did? Look at the query and notice the following:

  1. The WHERE clause selects only table data for countries that begin with the letters “A” by using the wildcard operator ‘%.’
  2. The GROUP BY clause groups the rows together by continent.
  3. The COUNT and SUM functions serve to (1) count the number of “A” countries each continent has, and (2) sum up the area, population and gdp figures for the “A” countries by continent.
  4. The ORDER BY clause orders the list, beginning with the continents that have the most "A" countries first (i.e., in DESC order).

Here is the result:

group-by-image-6.png
Now that's some great looking data

A Final Note About GROUP BY Syntax Order

There’s one last thing we haven’t talked about: The syntax order of a GROUP BY clause within a SELECT statement. When we added additional clauses to the SELECT statements above, you might have noticed that we didn't simply tack them onto the end. Clauses in SELECT statements must follow a specific syntax order. Otherwise, the query won’t work.

Here’s there order that clauses need to appear in:

  1. SELECT (SUM/COUNT functions go here)
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

The SQL GROUP BY clause always follows the WHERE clause and precedes the HAVING and ORDER BY clauses. You might not use all of the above clauses (depending on the type of query you’re writing). However, the ones you use must appear in this order.

Congratulations: Your Data Will Never Be Lonely Again

*SELECT cuddly FROM animals GROUP BY cuddly

Now that you understand how to use a GROUP BY clause, your data will never be lonely again. If you want practice your new SKILLZ. You can play around with the "world" table on the SQLZoo website. Try cutting-and-pasting the above examples into the query field and press "submit." Then you can GROUP BY whatever kind of data you want!

Have fun! And don't forget to read about my experience learning SQL with SQLZoo.net here.

Try SQLBot: It's Totally Free!

Writing SQL queries to produce awe-inspiring insights is one thing. Remembering to submit the query and push the results to your team on a daily, weekly, or monthly basis is another. With SQLBot, you'll never forget again. SQLBot submits your queries on autopilot! Then it sends the results to you and your team by SlackDaily—hourly, daily, weekly, whenever you want.

If you want to give SQLBot a try, click this link and create a free account now.

About the Author

undefined

Fascinated by emerging technologies, data science, SQL, and the laws and market trends that follow them, Jeremy Hillpot is a freelance technology writer whose background in consumer fraud litigation provides a unique perspective on new technologies in the fields of data science, investments, tech, cryptocurrency, and the law.