;
};
Some aggregations don't fit a simple `GROUP BY`. For example, you may want to show each customer's lifetime spend on a row-level breakout by product category, or compute each region's percentage of a grand total. Omni gives you four ways to handle these: an LOD field, a SQL window function, a CTE or derived table, or a spreadsheet.
In this guide, you'll learn about each of these methods, how they compare to each other, and when to choose each one.
## Before you start
If you're new to LOD fields, read [Level of detail fields](/analyze-explore/custom-fields/level-of-detail) first — this guide assumes basic familiarity with the field type.
## Available methods
The right choice usually comes down to whether the calculation needs to be reusable, where you're comfortable writing logic, and how much data is involved. Use the table below as a starting point; the [side-by-side comparison](#comparing-methods) goes deeper on the trade-offs.
| Method | Use this when... |
| -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Omni LOD field | The same calculation will appear in multiple workbooks, when it needs to respond to filters dynamically, or when you'd rather not write SQL. Cohorts, lifetime customer metrics, and percent-of-total calculations are good fits for this approach. |
| SQL window function | The calculation only matters inside one query and you're comfortable in SQL. Use for one-off analyses that don't need to be reusable. |
| CTE or derived table | You're working with a large dataset and benefit from materializing the intermediate result in your warehouse, or when the calculation involves multi-step transformations that don't fit cleanly into a single query. |
| Excel | Only when the data already lives in a spreadsheet or for a quick ad-hoc check. Excel isn't scalable and the logic isn't reusable outside the file. |
## Comparing methods
This table compares the four methods across the traits that usually drive the choice. Use it when you want to compare a specific dimension across all four options.
| Trait | Omni LOD field | SQL window function | CTE / derived table | Excel |
| :----------------- | :----------------------------------------------------------------------------------- | :----------------------------------------------------------------------------------------------- | :----------------------------------------------------------- | :------------------------------------------------------------ |
| **Primary use** | Aggregating at a specific level without changing query granularity. | Aggregating relative to the current row in a single query pass. | Creating temporary tables to join back to main data. | Aggregating across a range or within a pivot table. |
| **Where it lives** | UI or semantic layer; reusable across [workbooks](/analyze-explore/workbook-basics). | Inside a single SQL query. | In SQL; can be promoted to a [shared model view](/modeling). | A specific spreadsheet file. |
| **Flexibility** | Dynamic; responds to other filters while maintaining its fixed level. | Fixed to the query's current result-set granularity. | Static; requires rewriting SQL to change the grouped level. | Manual; requires updating ranges or refreshing pivot sources. |
| **Ease of use** | UI-driven; no code required. | Requires knowledge of window function parameters such as `OVER`, `PARTITION BY`, and `ORDER BY`. | Requires SQL and manual join logic. | Familiar for one-off math; hard to scale. |
| **Performance** | Optimized by Omni as part of query generation. | Database-dependent. | Best for massive datasets when pre-materialized. | Limited by local CPU and memory. |
| **Reusability** | Define once, use everywhere. | Trapped inside a single query. | Reusable if built into a shared model view. | Trapped inside a specific file. |
For the best performance on massive datasets, consider using a [derived table](/modeling/query-views/index) which can be materialized in your warehouse.
## Common scenarios
The three scenarios below cover the patterns that come up most often: bucketing customers by lifetime spend, building acquisition cohorts, and computing percent of total. Each is solved with all four methods side by side, so you can take a pattern you already know in one tool and find its equivalent in another.
### Customer lifetime spend
In this scenario, you want "total spend per customer" as a dimension you can use to bucket customers (`$0–100`, `$100–500`, and so on), even when viewing orders by product category.
The fields in the workbook's LOD editor would look like the following:
* **Field to aggregate** - `Sale price`
* **Aggregation** - `Sum`
* **Level of Detail Grouping** - `Fixed`
* **Fixed dimension(s)** - `User ID`
```sql theme={null}
SUM(sale_price) OVER (PARTITION BY user_id)
```
```sql theme={null}
WITH user_spend AS (
SELECT user_id,
SUM(sale_price) AS total
FROM orders
GROUP BY 1
)
SELECT o.*, us.total
FROM orders o
JOIN user_spend us ON o.user_id = us.user_id
```
In Excel, you'd have a formula where `A` is `User ID` and `C` is `Sale Price`:
```text theme={null}
=SUMIFS(C:C, A:A, A2)
```
### First-touch cohort date
In this scenario, you want each user's first order date so you can analyze performance by acquisition cohort.
The fields in the workbook's LOD editor would look like the following:
* **Field to aggregate** - `Order date`
* **Aggregation** - `Min`
* **Level of Detail Grouping** - `Fixed`
* **Fixed dimension(s)** - `User ID`
For a full walkthrough using this approach, see [Build cohort analysis using level of detail](/guides/patterns/build-cohort-analysis-using-level-of-detail).
```sql theme={null}
FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date ASC)
```
```sql theme={null}
WITH first_purchase AS (
SELECT user_id,
MIN(order_date) AS first_order_date
FROM orders
GROUP BY 1
)
SELECT o.*, fp.first_order_date
FROM orders o
JOIN first_purchase fp ON o.user_id = fp.user_id
```
In Excel, you'd have an array formula finding the minimum date (`B`) per user (`A`). In Excel versions without dynamic arrays, enter with `Ctrl+Shift+Enter`.
```text theme={null}
{=MIN(IF(A:A=A2, B:B))}
```
### Percent of total
In this scenario, you're viewing sales by region and want to know what percentage of the company's total sales each region represents.
The fields in the workbook's LOD editor would look like the following:
* **Field to aggregate** - `Sales`
* **Aggregation** - `Sum`
* **Level of Detail Grouping** - `Exclude`
* **Fixed dimension(s)** - `Region`
This returns the grand total on every row, so you can divide each row's sales by it to get percent of total.
```sql theme={null}
SUM(sales) OVER ()
```
```sql theme={null}
SELECT region,
sales,
(
SELECT SUM(sales)
FROM orders
) AS grand_total
FROM orders
```
In Excel, build it as a pivot:
1. Add `Sales` to **Values** twice.
2. Set the second to **Show Values As > % of Column Total**.
## Next steps
* Create your first LOD field in a [workbook](/analyze-explore/custom-fields/level-of-detail)
* Build a [cohort analysis](/guides/patterns/build-cohort-analysis-using-level-of-detail) using an LOD field
* Promote logic to a [derived table or query view](/modeling/query-views/index)
* Read Omni's [blog post on level of detail fields](https://omni.co/blog/level-of-detail-fields)