---
title: Aggregation Type (Term Mapping)
description: Aggregation Type sets an aggregate function, or Group By, on the term mapping of a target term.
---

[Skip to content](https://knowledge.bigenius-x.com/aggregation-type-term-mapping#main-content)

[Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en)

[![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/)

Open main navigation

Close main navigation

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new)
- [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal)

 How can we help you?

- There are no suggestions because the search field is empty.

1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en)
2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en)
3. [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)

# Aggregation Type (Term Mapping)

**Applies to**  
Platform: Microsoft SQL Server · Snowflake · Snowflake dbt · Databricks · Databricks dbt · Fabric Lakehouse · Fabric Warehouse · Oracle

Aggregation Type sets an aggregate function, or Group By, on the term mapping of a target term. Use it when a dataflow set must deliver one row per group, for example sales per supplier.

### Where to set it

Set Aggregation Type when you [edit a term mapping](https://knowledge.bigenius-x.com/edit-a-term-mapping?hsLang=en).

### Values and default

- `-` (default): no aggregation.
- `Group By`: the term is part of the GROUP BY clause.
- `Sum`, `Avg`, `Min`, `Max`, `Count`, `Distinct Count`: the mapped expression is wrapped in that function.
- `Expression`: the mapped expression contains the aggregate functions itself and is used as written. See [Expression](https://knowledge.bigenius-x.com/aggregation-type-term-mapping#expression).

### Rules

- When one term mapping of a dataflow set has an Aggregation Type, every mapped term of that set needs one. See [Dataflow Sets Aggregation Amount (Business Rule)](https://knowledge.bigenius-x.com/dataflow-sets-aggregation-amount-business-rule?hsLang=en).
- A dataflow with a multi-version load cannot aggregate. See [Aggregation with multi-version load (Business Rule)](https://knowledge.bigenius-x.com/aggregation-with-multi-version-load-business-rule?hsLang=en).
- Mart Dimension and Mart Hierarchy Flatten accept no aggregation.

### Expression

Use `Expression` when a target term needs a calculation across several aggregates, or an aggregate function that the list does not offer:

- Ratios and weighted averages, for example `sum(s1.SALES_AMOUNT) / NULLIF(sum(s1.QUANTITY), 0)`.
- Conditional totals, for example `sum(CASE WHEN s1.QUANTITY > 0 THEN 1 ELSE 0 END)`.
- Aggregate functions of the platform, for example `STRING_AGG` on Microsoft SQL Server.

The generator writes the expression into the generated statement unchanged. It adds no aggregate function and leaves the term out of the GROUP BY clause. For the Dataflow Sets Aggregation Amount rule, the term counts as aggregated. The generator does not check whether the expression is valid in an aggregated query.

### Example

A Mart Fact on Microsoft SQL Server delivers sales per supplier. Its dataflow set maps these terms; `s1` is the alias of the sales source, and `FK_..._ITEM_ID` stands for the full name of the foreign key term:

- `FK_..._ITEM_ID`, filled from `SUPPLIER_ID`: `Group By`
- `SALES_AMOUNT`, mapped to `s1.SALES_AMOUNT`: `Sum`
- `AVG_UNIT_AMOUNT`, mapped to `sum(s1.SALES_AMOUNT) / NULLIF(sum(s1.QUANTITY), 0)`: `Expression`

The generated source view, shortened:

```
SELECT
     CAST([s3].[SUPPLIER_ID] AS DECIMAL(38)) AS [FK_..._ITEM_ID]
    ,SUM([s1].[SALES_AMOUNT]) AS [SALES_AMOUNT]
    ,sum([s1].[SALES_AMOUNT]) / NULLIF(sum([s1].[QUANTITY]), 0)
        AS [AVG_UNIT_AMOUNT]
FROM ...
GROUP BY
     CAST([s3].[SUPPLIER_ID] AS DECIMAL(38))
```

`Sum` wraps `SALES_AMOUNT` in `SUM()`. The expression of `AVG_UNIT_AMOUNT` appears as written, with its lower-case `sum`. Only the term with `Group By` is in the GROUP BY clause.

### Related articles

- Reference: [Dataflow Sets Aggregation Amount (Business Rule)](https://knowledge.bigenius-x.com/dataflow-sets-aggregation-amount-business-rule?hsLang=en), [Aggregation with multi-version load (Business Rule)](https://knowledge.bigenius-x.com/aggregation-with-multi-version-load-business-rule?hsLang=en)
- See also: [Edit a Term Mapping](https://knowledge.bigenius-x.com/edit-a-term-mapping?hsLang=en), [Mart Fact](https://knowledge.bigenius-x.com/mart-fact?hsLang=en)

- [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content)

    - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines)
    - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working)
    - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account)
- [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en)
- [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en)
- [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content)

    - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration)
    - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions)
    - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features)
    - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects)
    - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches)
    - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections)
    - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview)
    - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps)
    - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms)
    - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping)
    - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships)
    - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality)
    - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes)
    - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling)
    - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts)
    - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings)
    - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace)
- [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content)

    - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks)
    - [Databricks dbt](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks-dbt)
    - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse)
    - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse)
    - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server)
    - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle)
    - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake)
    - [Snowflake dbt](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake-dbt)
    - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts)
    - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders)
    - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment)
    - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment)
    - [Deployment with an Azure DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azure-devops-pipeline)
    - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment)
    - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment)
    - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control)
    - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow)
    - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type)
    - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties)
    - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms)
- [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content)

    - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations)
- [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content)

    - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches)
    - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases)
    - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules)
    - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules)
- [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en)
- [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content)

    - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application)
    - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application)
    - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators)
- [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content)

    - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs)
    - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits)

- [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en)
- [Customer Portal](https://knowledge.bigenius-x.com/customer-portal)
- [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login)

[![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com)

<https://www.linkedin.com/company/bigenius> <https://www.youtube.com/@biGENIUS_DWA>

All rights reserved © 2025, biGENIUS AG