Learn how to group data in SQL using the

GROUP BY

Those who work with data know that grouping data can be a chore. If you are struggling with a spreadsheet or some other tool to group various sets of data, read this tutorial and learn how to do the work more easily (and faster) using SQL. We’ll be working on marketing sample data.

Have you ever found yourself in a situation where you need to analyze data and present the results in a form other than the one in which the data is stored? In this tutorial, you will see how helpful SQL can be for effective data manipulation when it comes to grouping data in SQL.

Problem

Scenario

You work for a worldwide digital influencer marketing agency. The agency has branches all over the world, and the CMO has decided to carry out a promotion planning event. The plan is to host a forum in each of the locations with as many influencers attending.

Each influencer has a manager who takes care of the influencer during events, provides information, and handles settlements. One manager can take care of only one influencer at a time. Managers with more than one influencer to serve will have to recruit an event assistant. 

You have been requested for the following information:

1. The number of influencers per location (to determine how big the event is going to be)
2. The number of influencers per manager at each location (to examine how many event assistant will have to be recruited)

This information is internally stored in the agency’s SQL database, which contains a table with data on associated influencers.

Your job is to analyze the data stored in the influencer table and provide the requested information to the CMO.

Sample Data Table

The table below is named

influencer

In order to graphically present the grouping process, you have to sort sample data by location and influencer_manager. The

influencer

Let's start with the following query:

SELECT *
FROM influencer 
ORDER BY location, influencer_manager

Now, the

influencer

As you can see, the data in the table is sorted in ascending order, first by location and then by influencer’s manager.

Solution

What is the SQL GROUP BY Clause?

The tasks defined by the CMO all require data grouping. In SQL, data grouping is performed using the

GROUP BY

The SQL

GROUP BY

SELECT

Syntax of GROUP BY Clause

The syntax of the

GROUP BY

SELECT count(column_name1), column_name2, column_name3, ... ,
FROM table_name
WHERE condition
GROUP BY column_name2, column_name3, ... , 
ORDER BY column_name2, column_name3, ... , 

When it comes to syntax, it’s important to keep in mind the placement of the GROUP BY clause in the SELECT statement. Always write the GROUP BY statement after the FROM and/or WHERE clauses and before the ORDER BY clause (if one exists).

As you can see, to use the

GROUP BY

There are many Internet resources to learn SQL, like Udemy, Coursera, edX but they often lack of interactivity and may be too challenging for beginners.

I recommend sites where you gain hands-on experience in writing code from the very first exercise, such as in the SQL Basics by Vertabelo Academy.

You will be amazed how fast you can progress through practice!

If you’re already familiar with writing basic

SELECT

GROUP BY

SQL GROUP BY Examples

It’s time to solve the tasks presented in the scenario. First, you’ll have to write a basic GROUP BY query. Afterward, you’ll go through a slightly more complicated example to see how grouping can be performed on more than one table column.

Task #1: Get the Number of Influencers Per Location

The Query:

SELECT location, count(location) as number_of_influencers
FROM influencer 
GROUP BY location
ORDER BY location

Let’s take a closer look at the process of building the above query:

1. SELECT the location column. We’ll group your data using this column.

SELECT location
FROM influencer 

2. Add another column to the SELECT statement by applying the COUNT function to one of the table columns to get the number of influencers.

Since  the COUNT function is used to count the table rows, it doesn't matter which table column you apply it to.

SELECT location, count(location) as number_of_influencers
FROM influencer

3. The previous query will not work without a GROUP BY clause, because it contains an aggregate function. Therefore, insert a GROUP BY clause, and use the location column for grouping.

Since the column contains five different values (Brighton, London, Rome, Los Angeles, and Sydney), the final query result will have five rows.

SELECT location, count(location) as number_of_influencers
FROM influencer 
GROUP BY location

The table below displays how the grouping is done: the data is grouped based on distinct values in the location column, where each drawn rectangle contains data for a particular location. In other words, each rectangle represents one data group.

The COUNT function will count the number of rows within each group and return the number of influencers within a location.

4. Apply an

ORDER BY

SELECT location, count(location) as number_of_influencers
FROM influencer
GROUP BY location
ORDER BY location

Query Result:

Task #2: Get the Number of Influencers Per Manager at Each Location

Let’s verify whether managers will have to hire assistants for the forum.

The Query:

SELECT influencer_manager, location, count(influencer_name) as number_of_influencers
FROM influencer
GROUP BY influencer_manager, location,  
ORDER BY influencer_manager

Now, we’ll take a closer look at the process of building the above query:

1.

SELECT

influencer_manager

location

SELECT influencer_manager, location,
FROM influencer

2. Just as we did in the previous example, add another column to the SELECT statement by applying the COUNT function to one of the table columns (to get the number of influencers per manager).

SELECT influencer_manager, location, count(influencer_name) as number_of_influencers
FROM influencer

3. Again, the query from the previous step won’t work without a GROUP BY clause since it contains an aggregate function. You should now apply a GROUP BY clause to both the influencer_manager and location columns.

So, first, you group data by managers. Next, you group those managers by location, effectively creating manager subgroups within the location groups.

SELECT influencer_manager, location, count(influencer_name) as number_of_influencers
FROM influencer
GROUP BY influencer_manager, location

The

COUNT

4. Apply an ORDER BY clause on the influencer_manager column to sort the query results in ascending order by the name of a manager.

SELECT influencer_manager, location, count(influencer_manager) as number_of_influencers
FROM influencer
GROUP BY influencer_manager, location 
ORDER BY influencer_manager

Query Result:

Et voilà! As you can see, there are managers with more than one local influencer, and there are managers with influencers from different locations all over the world. 

Thanks to this knowledge, we can now, for example, plan the budget for the hiring managers’ assistants or spot managers who’d have to decide on attending only one event location.  

Summary

In this article, I’ve reviewed the basics of the SQL

GROUP BY

Being able to group data easily is a very useful skill when working with datasets of different sizes, and you know now how the SQL

GROUP BY

So if you do have some basic SQL knowledge, it would be a pity if you don't upgrade it with

GROUP BY

Thank you for reading! ;-)