How to use SQL GROUP BY for arranging identical values in groups

If you want to combine rows with identical values into a group, the SQL GROUP BY statement is the right choice. It is typically used in combination with aggregate functions.

What is SQL GROUP BY?

In Structured Query Language, the SQL GROUP BY statement is used to combine rows with identical values in a group. It is used with the SQL command SELECT and follows the WHERE statement. SQL GROUP BY is often used in combination with functions such as SQL AVG(), SQL COUNT(), MAX(), MIN() or SUM(). This allows you to perform calculations and display the results within your table. The statement is particularly helpful for determining sequences or relating values to each other.

Syntax and function

The basic syntax of SQL GROUP BY is:

SELECT column1, column2, column3, ... 
FROM name_of_table 
GROUP BY column1, column2, column3, ...;

However, the version with a WHERE clause, which allows you to specify certain conditions, is much more common. This version looks like this:

SELECT column1, column2, column3, ... 
FROM name_of_table 
WHERE condition 
GROUP BY column1, column2, column3, ... 
ORDER BY column1, column2, column3, ...;

Example of use with COUNT()

To illustrate how you can use SQL GROUP BY, let’s create a simple table called ‘Customer List’. This contains columns for a customer number, the name, the location and the items purchased:

Now we can use SQL GROUP BY in combination with the COUNT() function, for example, to list how many customers come from which cities. Here is the corresponding code:

SELECT Location, COUNT(*) AS Number 
FROM Customer_list 
GROUP BY Location;

The result is:

Here we use the SQL aliases to display the results as a ‘number’.

Used with SUM()

In the next example, we use SQL GROUP BY in combination with SUM() to determine and display how many items were ordered from Leicester. We use this code for this:

SELECT Location, SUM(Item) AS Total 
FROM Customer_list 
WHERE Location = 'Leicester' 
GROUP BY Location;

The result we obtain is:

Used with ORDER BY

A combination with ORDER BY is also possible. For our table, we sort by the highest number of items ordered per customer and per city. We start with the location where a customer has purchased the most items. The corresponding code for combining SQL GROUP BY with the MAX() function and ORDER BY function is:

SELECT Location, MAX(item) AS Most 
FROM Customer_list 
GROUP BY Location ORDER BY Most DESC;

And the corresponding issue:

Used with HAVING

You can also combine SQL GROUP BY with SQL HAVING. In the following example, we remove customers from the list whose customer number is less than 1300. We then sort the remaining customers according to the number of items they have ordered in ascending order. The code looks like this:

SELECT location, customer number, MIN(article) AS fewest 
FROM Customer list 
GROUP BY Location, Customer number HAVING Customer number > 1300;

The resulting table is:

Alternatives to SQL GROUP BY

A popular alternative to SQL GROUP BY is the PARTITION BY statement. The difference is that all original values are retained and displayed. Additionally, many of the aggregate functions mentioned above also work without SQL GROUP BY.