Aggregation Concept

Aggregation in Excel

Pivoting in an Excel pivot table: It refers to transforming a detailed table through aggregation calculations into a more concise result table, where the table structure is adjusted by changing the position of row and column fields.

Aggregation in SQL

For example, to sum up the sales volume of each customer ID in 2020 and retain only the data of customer ID whose total sales volume exceeds $20,000, you can write the SQL statement as follows.

SELECT Customer ID, SUM (Sales Volume)
FROM xxx
WHERE TEAR(Order Date) =2020
GROUP BY Customer ID HAVING SUM (Sales Volume) >20,000;

Structurally, this SQL statement includes filtering (WHERE/HAVING), grouping (based on GROUP BY), and finally aggregation (following SELECT).

Procedurally, this SQL statement embodies an aggregation process from a detailed table to a cross table.

Aggregation in FineBI

1. The correlation between aggregation and an Excel pivot table: 

The pivot table in Excel is actual a kind of aggregation tool. The process of placing data into a pivot table and summarizing information based on certain categories is essentially data aggregation.

2. Aggregation in a FineBI component: 

For example, a table recording the sales facts of commodities contains the sales volume of each day. To calculate the total sales volume of each month, you need to group the data by month and then sum up the sales volume of all days. This process is called aggregation.

3. Advantages and functions: 

Through aggregation, you can simplify a large amount of complex data and extract overall information in a more understandable and analyzable form to make wiser decisions.

1. Direct aggregation

Direct aggregation can be used in simple indicators, for example, KPIs, which are based on direct aggregation and describe the business scale.

2. Aggregation calculation

Sometimes scale indicators may struggle to unveil the quality underpinning business operations. In this case, aggregation calculations often serve as a supplement, for example, Profit Margin=SUM_AGG(Profit)/SUM_AGG(Sales Volume).

If direct aggregation is used, for example, you only need to sum up a single value, you can drag and drop the field to the indicator. It will automatically sum up. Generally, aggregate functions will be used in aggregation calculations.

For details, see Overview of Aggregate Functions.

Q: What are the aggregate functions?

A: For details, see Overview of Aggregate Functions.

Q: Why aggregate functions can only be used in components?

A: After you use aggregate functions, the calculation fields can automatically adjust with the switch of analysis dimensions.

Calculations on the data editing page are performed at the individual row level. You can specify dimensions to perform aggregation calculations on the component editing page.

Q: What are the differences between SUM_AGG(A/B) and SUM_AGG(A)/SUM_AGG(B)?

A: The former is to divide first and then sum up, and the latter is to sum up separately to get individual totals first and then divide.

Q: Can aggregate functions be nested outside of other aggregate functions?

A: No.