# 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](https://www.ionos.com/digitalguide/server/configuration/sql-introduction-with-examples/), the SQL `GROUP BY` statement is used to combine rows with identical values in a group. It is used with the [SQL command](https://www.ionos.com/digitalguide/server/configuration/sql-commands/) `SELECT` and follows the `WHERE` statement. SQL `GROUP BY` is often used in combination with functions such as [SQL AVG()](https://www.ionos.com/digitalguide/server/configuration/sql-avg/), [SQL COUNT()](https://www.ionos.com/digitalguide/server/configuration/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:

```sql
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:

```sql
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:

<table>
  <thead>
    <tr>
      <th>Customer Number</th>
      <th>Name</th>
      <th>Location</th>
      <th>Items</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>1427</td>
      <td>Smith</td>
      <td>New York</td>
      <td>13</td>
    </tr>
    <tr>
      <td>1377</td>
      <td>Johnson</td>
      <td>Los Angeles</td>
      <td>9</td>
    </tr>
    <tr>
      <td>1212</td>
      <td>Brown</td>
      <td>Los Angeles</td>
      <td>15</td>
    </tr>
    <tr>
      <td>1431</td>
      <td>Davis</td>
      <td>Chicago</td>
      <td>22</td>
    </tr>
    <tr>
      <td>1118</td>
      <td>Wilson</td>
      <td>New York</td>
      <td>10</td>
    </tr>
  </tbody>
</table>

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:

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

The result is:

<table>
  <thead>
    <tr>
      <th>Location</th>
      <th>Count</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Chicago</td>
      <td>1</td>
    </tr>
    <tr>
      <td>New York</td>
      <td>2</td>
    </tr>
    <tr>
      <td>Los Angeles</td>
      <td>2</td>
    </tr>
  </tbody>
</table>

Here we use the [SQL aliases](https://www.ionos.com/digitalguide/server/configuration/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 Los Angeles. We use this code for this:

```sql
SELECT Location, SUM(Item) AS Total 
FROM Customer_list 
WHERE Location = 'Los Angeles' 
GROUP BY Location;
```

The result we obtain is:

<table>
  <thead>
    <tr>
      <th>Location</th>
      <th>Total</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Los Angeles</td>
      <td>24</td>
    </tr>
  </tbody>
</table>

## 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:

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

And the corresponding issue:

<table>
  <thead>
    <tr>
      <th>Location</th>
      <th>Most</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Chicago</td>
      <td>22</td>
    </tr>
    <tr>
      <td>Los Angeles</td>
      <td>15</td>
    </tr>
    <tr>
      <td>New York</td>
      <td>13</td>
    </tr>
  </tbody>
</table>

## Used with HAVING

You can also combine SQL `GROUP BY` with [SQL HAVING](https://www.ionos.com/digitalguide/server/configuration/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:

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

The resulting table is:

<table>
  <thead>
    <tr>
      <th>Location</th>
      <th>Customer Number</th>
      <th>Fewest</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>Los Angeles</td>
      <td>1377</td>
      <td>9</td>
    </tr>
    <tr>
      <td>New York</td>
      <td>1427</td>
      <td>13</td>
    </tr>
    <tr>
      <td>Chicago</td>
      <td>1431</td>
      <td>22</td>
    </tr>
  </tbody>
</table>

## 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`.

Tip A database tailored to your needs: With [SQL Server Hosting](https://www.ionos.com/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS, you can choose between MSSQL, MySQL, or MariaDB. In every case, you benefit from outstanding performance, strong security features, and personalized advice.


This is a markdown version of: [https://www.ionos.com/digitalguide/server/configuration/sql-group-by/](https://www.ionos.com/digitalguide/server/configuration/sql-group-by/) for AI/LLM consumption.