When you’re working with aggregate functions in
SQL, you may want to group rows together by common column values. For example, you may want to get a list of branch names for your business, as well as the number of employees who work for those branches.
That’s where the
GROUP BY clause comes in.
GROUP BY column is used in addition to a
SELECT statement to group rows together when you’re using an aggregate function.
In this tutorial, we are going to break down the basics of the
SQL GROUP BY column clause and discuss how you can use it in your queries.
Aggregate Functions Refresher
Often—when you’re working with a database—you are not looking to see the actual data within the database. Instead, you may want information about the data. For example, you may want to know the number of unique products your business sells or the maximum score on a leaderboard.
There are a number of functions built-in to
SQL that allow you to get this information. These are called aggregate functions. For example, let’s say you wanted to find out how many employees are sales associates, you could use the
COUNT function. The
COUNT function counts and returns the number of rows that meet a specific set of criteria.
Here’s an example of a
SELECT COUNT(title) FROM employees WHERE title = 'Sales Associate';
Our query returns the following:
Here is a list of the other aggregate functions often used in
AVG: Get the average of a column of values;
SUM: Get the total amount of all values in a column;
MIN: Get the lowest number in a column;
MAX: Get the highest number in a column.
These aggregate functions use the same syntax as above, and return their respective results, as noted in our list. If you’re looking to learn more about aggregate functions, you can read our in-depth article on them here.
SQL Group By
In the query we wrote above, we got the number of sales associates who worked for our business.
Now, let’s say that instead of finding the number of sales associates we have, we want to find the total number of employees with each title given to the workforce. In other words, we want to know how many sales associates we have, how many marketing directors we have, and so on.
In this situation, we can use the
GROUP BY clause with a
SELECT statement. Here’s the syntax for a
GROUP BY clause:
SELECT COUNT(column1_name), column2_name FROM table1_name GROUP BY column2_name;
GROUP BY clause goes at the end of our query and is used to tell our query what column we want our data to be grouped in. Let’s use an example to illustrate how this works. Let’s say that we want to know how many employees hold certain titles, like we discussed above. We could use the following query:
SELECT title, COUNT(title) FROM employees GROUP BY title;
The query result is as follows:
|Senior Sales Associate||1|
|Vice President of Sales||1|
GROUP BY query has returned a list of unique titles held by employees, as well as the number of employees who hold that title.
GROUP BY clause should be used in any query where you are looking to get more information than just what’s returned from the aggregate function.
So, if you are looking to get the number of customers you have, you only need to run a regular query. Here’s an example of a query that would return this information:
SELECT COUNT(name) FROM customers;
Our query returns the following result set:
But if you want to know how many customers are on each of your loyalty plans, you would need to use a
GROUP BY statement. Here is an example of a query that would get a list of loyalty plans and the number of customers on each plan:
SELECT loyalty_plan, COUNT(loyalty_plan) FROM customers GROUP BY loyalty_plan;
Our query returns the following:
SQL Group By Multiple Columns
Now, if we wanted to, we could perform a
GROUP BY on multiple columns. For example, if we wanted to get a list of how many employees we had at each branch with certain titles, we could use the following query:
SELECT branch, title, COUNT(title) FROM employees GROUP BY branch, title;
This is the result of our query:
|Albany||Vice President of Sales||1|
|San Francisco||Sales Associate||1|
|San Francisco||Senior Sales Associate||1|
Our query has created a list of the title held by each employee, as well as the number of people who hold that title, grouped by the branch for which each employee works, and their titles.
SQL GROUP BY clause can be used to group the result of a particular query that uses aggregate functions by a certain column. For example, if you want to get a list of car manufacturers and the number of cars each has sold to your business as company cars, you could use a
GROUP BY clause. Learning to use the
SQL GROUP BY clause brings you one step closer to being an