# Aggregate Functions

Use aggregate functions in a `GROUP BY` clause in SOQL queries to generate reports for analysis. Aggregate functions include `AVG()`, `COUNT()`, `MIN()`, `MAX()`, `SUM()`, and more.

You can also use aggregate functions *without* using a `GROUP BY` clause. For example, you could use the `AVG()` aggregate function to find the average `Amount` for all your opportunities.

```soql
SELECT AVG(Amount)
FROM Opportunity
```

However, these functions become a more powerful tool to generate reports when you use them with a `GROUP BY` clause. For example, you could find the average `Amount` for all your opportunities by campaign.

```soql
SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
```

This table lists all the aggregate functions supported by SOQL.

:::note
All aggregate functions ignore null values, except for `COUNT()` and `COUNT(Id)`.

`COUNT(fieldname)` is different from `COUNT()`. `COUNT(fieldname)` does ignore null values.
:::

| Aggregate Function               | Description                                                                                                                                                                                   |
| -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `AVG()`                          | Returns the average value of a numeric field.                                                                                                                                                 |
| `COUNT()` and `COUNT(fieldName)` | Returns the number of rows matching the query criteria.                                                                                                                                       |
| `COUNT_DISTINCT()`               | Returns the number of distinct non-`null` field values matching the query criteria.                                                                                                           |
| `MIN()`                          | Returns the minimum value of a field. If you use the `MIN()` or `MAX()` functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order. |
| `MAX()`                          | Returns the maximum value of a field. Available in API version 18.0 and later.                                                                                                                |
| `SUM()`                          | Returns the total sum of a numeric field.                                                                                                                                                     |

Example `AVG()`:

```soql
SELECT CampaignId, AVG(Amount)
FROM Opportunity
GROUP BY CampaignId
```

Example using `COUNT()`:

```codesegment
SELECT COUNT()
FROM Account
WHERE Name LIKE 'a%'
```

Example using `COUNT(fieldName)`:

```codesegment
SELECT COUNT(Id)
FROM Account
WHERE Name LIKE 'a%'
```

**Note:** `COUNT()` and `COUNT(Id)` in SOQL are similar to `COUNT(*)` in SQL.

Example `COUNT_DISTINCT()`:

```soql
SELECT COUNT_DISTINCT(Company)
FROM Lead

```

**Note:** `COUNT_DISTINCT(fieldName)` in SOQL is equivalent to `COUNT(DISTINCT fieldName)` in SQL. .

Example `MIN()`:

```soql
SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact
GROUP BY FirstName, LastName
```

Example `MAX()`:

```soql
SELECT Name, MAX(BudgetedCost)
FROM Campaign
GROUP BY Name
```

Example `SUM()`:

```soql
SELECT SUM(Amount)
FROM Opportunity
WHERE IsClosed = false AND Probability > 60
```

You can't use a `LIMIT` clause in a query that uses an aggregate function, but does not use a `GROUP BY` clause. For example, the following query is invalid:

```soql
SELECT MAX(CreatedDate)
FROM Account LIMIT 1
```