## Custom SQL Aggregations

_Custom SQL Aggregations_ are a powerful new feature that offer you more flexibility than traditional simple Aggregations such as SUM, MAX, and COUNT when defining your billing metrics. As an additional option in the existing Aggregation layer, Custom SQL Aggregations can be used in combination with Segmentation and any existing pricing model. This topic explains how to create Custom SQL Aggregations and provides some examples:

- [Creating Custom SQL Aggregations](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#creating-custom-sql-aggregations)
- [Creating Custom SQL Queries](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#creating-custom-sql-queries)
- [Custom SQL Aggregations - Examples](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#custom-sql-aggregations-examples)
  - [Example 1 - Monthly Billing, Daily Rating](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#example-1-monthly-billing-daily-rating)
  - [Example 2 - Group by Dimension](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#example-2-group-by-dimension)
  - [Example 3 - Reserved Instances (RIs) with Overages](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#example-3-reserved-instances-ris-with-overages)
  - [Example 4 - Custom SQL Descriptions](https://docs.m3ter.com/#example-4-custom-sql-descriptions)
- [Supported SQL Functions](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#supported-sql-functions)

**Custom SQL Aggregations in Preview Release.** Please note that the Custom SQL Aggregations feature is currently available in Preview release version:
- See [Feature Release Stages](https://docs.m3ter.com/guides/getting-started/feature-release-stages) for Preview release definition and guidance.

## Creating Custom SQL Aggregations
To create Custom SQL Aggregations in the m3ter Console you can follow similar steps as when you create other types of Aggregation using the standard aggregation methods, such as SUM, COUNT, MAXIMUM, and so on. **To create a Custom SQL Aggregation:**

1. Select **Metering>Aggregations**. The **Aggregations** page opens.
2. In the **Product** drop-down, select the Product for which you want to create the new Custom SQL Aggregation.
3. Select **Create aggregation**. The **Aggregations>Create** page opens.
4. Configure **Aggregation** **details** and enter:
   - **Name**. ( _Required_)
   - **Code**. ( _Required_)
   - **Accounting product**. Use the drop-down to select a Product. ( _Optional_)
     - For accounting purposes, you can use this to link to a specific Product any usage line items on Bills that result from pricing a Plan using this Aggregation.

5. Configure **Meter settings**:
   - **Meter**. Select the **Meter** whose **Data Field** or **Derived Field** you want to use as the basis for the Aggregation. 
   - **Target Field**. When you select a **Meter**, the **Target Field** drop-down list automatically populates with the **Codes** of any fields set up on that **Meter**:

6. Configure **Aggregation settings**:
   - **Aggregation**. Select **Custom SQL** for the aggregation method. The page adjusts to show an **SQL** text entry box where you can enter your SQL query expression. For example:
   ```sql
   SELECT SUM(measure) AS value, DATE_TRUNC('day', ts) AS date
   FROM measurements
   GROUP BY DATE_TRUNC('day', ts)
   ```
   - **Unit**. This will be used as a label for billing to indicate to your customers what they are being charged for.
   - **Quantity per unit**. Enter the quantity by which you want to charge for the measured value.
   - **Rounding**. Specifies how you want m3ter to deal with non-integer, that is fractional number, Aggregation values.

7. Select **Create aggregation**. The **Aggregation details** page opens.

## Creating Custom SQL Queries
This section provides details and guidance for creating queries for your Custom SQL Aggregations. Please review this section in preparation for creating Custom SQL queries:
- [Measurements Table](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#measurements-table)
- [Key Points](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#key-points)
- [Limit on GROUP BY Clause](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#limit-on-group-by-clause)

### Measurements Table
Custom SQL queries should be run against the **measurements** table. This table is provided to your SQL query and is already limited to the appropriate Account, time period, and so on that the Aggregation needs to run over.

| Column | Type | Used for |
| --- | --- | --- |
| measure | Float64 | The value of the target field you selected. |
| ts | DateTime64 | The ts value of the measurement in UTC timezone. |
| ets | DateTime64 | The ets value of the measurement in UTC timezone. |
| uid | String | The uid value of the measurement. |
| received_at | DateTime64 | When the data was received in UTC timezone. |
| account_id | UUID |  |
| dimensions | Map of Strings | String dimension values for the measurement. |

### Key Points
When creating your SQL queries, please note the following key points:
- The **result(s)** of the query must be returned as a numeric column called **value**. 
- Queries can return up to 1,000 rows. 
- Dimensions can be accessed using map notation. For example, to access the value of a dimension called “region”, you would write `dimensions['region']`.

### Limit on GROUP BY Clause
The limit on the use of GROUP BY clauses for a Custom SQL Aggregation depends on whether or not the Aggregation is segmented.

## Custom SQL Aggregations - Examples
This section offers some example billing use cases fulfilled using Custom SQL Aggregations:
- [Example 1 - Monthly Billing, Daily Rating](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#example-1-monthly-billing-daily-rating)
- [Example 2 - Group by Dimension](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#example-2-group-by-dimension)
- [Example 3 - Reserved Instances (RIs) with Overages](https://docs.m3ter.com/guides/usage-data-aggregations/custom-sql-aggregations#example-3-reserved-instances-ris-with-overages)

### Example 1 - Monthly Billing, Daily Rating
```sql
SELECT SUM(measure) AS value, DATE_TRUNC('day', ts) AS date
FROM measurements
GROUP BY DATE_TRUNC('day', ts)
```

### Example 2 - Group by Dimension
```sql
SELECT SUM(measure) AS value, dimensions['vehicle_id'] as vehicle_id
FROM measurements
GROUP BY dimensions['vehicle_id']
```

### Example 3 - Reserved Instances (RIs) with Overages
```sql
SELECT SUM(
  CASE((measure - counters['ri']) > 0) WHEN 1 THEN (measure - counters['ri']) ELSE 0 END
) AS value
FROM measurements
```

### Example 4 - Custom SQL Descriptions
If you are using Custom SQL Aggregations to price Plans, this feature offers you wide flexibility when setting up Billing line item descriptions.

#### Default Custom SQL Descriptions
If you _do not_ define a description on the pricing you configure for a Plan using a Custom SQL Aggregation, then a default description is used that includes any key/value pairs used in the GROUP BY clause: _<Aggregation name> (<key/value 1>,<key/value 2>,_ <key/value 3> _…)_.

## Supported SQL Functions
The following functions are supported in Custom SQL Aggregations:
- `AVG`
- `SUM`
- `COUNT`
- `MAX`
- `MIN`
- `ROUND`
- `FLOOR` 
- `CEIL` 
- `CAST` 
- `COALESCE` 
- `LEAST` 
- `GREATEST` 
- `DATE_TRUNC` 
- `FIRST_VALUE` 
- `LAST_VALUE` 
- `ROW_NUMBER` 
- `ORDER BY`