# GROUP

The `GROUP` clause is used to group records by one or more columns.

The `GROUP` clause is used to aggregate data based on one or more fields. It is particularly useful when you want to perform calculations on groups of data, such as counting the number of records, calculating averages, or finding sums for each group.

This is often used in reporting and data analysis to summarize data in a meaningful way. More specifically, it is used to:

- Aggregating data: When you need to calculate aggregate values like SUM, COUNT, AVG, MIN, or MAX for each group of data.
- Data summarisation: When you want to summarise data into categories or groups.
- Reporting: When generating reports that require grouped data, such as sales reports by region or department.

This clause is followed with either:

* `BY` to specify certain fields to group by, or
* `ALL` to group every selected row into a single aggregate.

_(since v3.2.5)_

A projection made entirely of bare zero-argument [`count()`](/docs/reference/query-language/functions/database-functions/count.md) implies `GROUP ALL`, so `SELECT count() FROM person` and `SELECT count() FROM person GROUP ALL` are equivalent. See [Bare `count()` implies `GROUP ALL`](/docs/reference/query-language/statements/select.md#bare-count-implies-group-all) for the cases that stay per-row (`count(field)`, `*`, `SELECT VALUE`, `SPLIT`, and so on).

## Syntax

```syntax title="Clause Syntax"
GROUP [ BY @fields | ALL ]
```

## Aggregate functions

A [number of functions](/docs/reference/query-language/functions/database-functions/#aggregate-functions) can be used inside a `GROUP BY` query to perform an operation on the data as a whole as opposed to per record.

For example, the [`math::sum()`](/docs/reference/query-language/functions/database-functions/math.md#mathsum) function can be used on an array of numbers to calculate their final sum.

```surql
-- Returns 30
math::sum([
    {
        name: "Billy",
        money: 10
    },
    { 
        name: "Tommy",
        money: 20
    }
].money);
```

Attempting to use the same function inside a `SELECT` query will not work as `math::sum()` expects an array of numbers but only receives a single integer each time it is called.

```surql
SELECT 
    name AS names, 
    math::sum(money) AS money 
FROM [
    {
        name: "Billy",
        money: 10
    },
    { 
        name: "Tommy",
        money: 20
    }
];
```

If the data is aggregated with a `GROUP` clause, the query will no longer fail.

```surql
SELECT 
    name AS names,
    math::sum(money) AS money
FROM [
    {
        name: "Billy",
        money: 10
    },
    { 
        name: "Tommy",
        money: 20
    }
] GROUP ALL;
```

```surql title="Output"
[
	{
		money: 30,
		names: [
			'Billy',
			'Tommy'
		]
	}
]
```

## Longer example

```surql
SELECT
    product_id,
    region,
    math::sum(amount) AS total_sales
FROM
    sales
GROUP BY
    product_id, region;
```

Explanation:
- `SELECT product_id, region, math::sum(amount) AS total_sales`: This selects the `product_id` and `region` columns and calculates the total sales amount for each group. The `AS` clause is used to rename the calculated column to `total_sales`.

- `FROM sales`: This specifies the table from which to retrieve the data. Using the `FROM` clause, we specify the table `sales` to retrieve the data from.

- `GROUP BY product_id, region`: This groups the results by product_id and region, so the `math::sum()` function calculates the total sales for each unique combination of product_id and region.

This query will return a result set where each row represents a unique combination of `product_id` and `region`, along with the total sales amount for that combination. This is useful for understanding how different products are performing in different regions.

```surql
		SELECT
	count() AS total,
	math::mean(age) AS average_age,
	gender,
	country
FROM rams
GROUP BY gender, country;
```

## Latest record per group

When you need the most recently modified record for each value of a field, group by that field and use [`.map()`](/docs/reference/query-language/functions/database-functions/array.md#arraymap) to run a nested `SELECT` per group:

```surql
(SELECT id, role FROM person GROUP BY role).map(|$o| {
    SELECT * FROM ONLY $o.id ORDER BY modified_at DESC LIMIT 1
});
```

`GROUP BY` collects record ids into an array, after which the inner query orders those records and returns the latest. For a worked example with sample data, see [Latest record per group](/docs/learn/querying/concepts-and-guides/subqueries-and-advanced-patterns.md#latest-record-per-group).