Aggregate functions and pivoting can be used to group and aggregate data in a database table.

The basic functionality is best explained by a simple example.

We have a table (not normalized for the sake of simplicity) which contains employees of a company. Each employee has a name, belongs to a department, has an experience level and a salary.

Now let's see how we can squeeze some useful information out of this dataset.

Salaries by employee

This is the simplest form of information. Just display the name of the employee together with its salary. This doesn't need any aggregate function or pivoting at all.

Table

The table consists of two columns, one for the name and one for the salary, both without an aggregate function.

Name column configuration

Salary column configuration:

Chart

Chart configuration

Salary value column configuration

Summarized salaries grouped by department

Table

Department column configuration:

The employees are grouped by the department.

Salary column configuration:

Because we already have grouped the employees by department, all other columns have to use an aggregate function. In this case we want to summarize the salaries of employees within a department.

Chart

Chart configuration

Rows are automatically grouped by the column selected as X axis label.

Salary value column configuration

Average salaries grouped by department

Same configuration as in the above example, except the aggregate function of the salary column is set to avg instead of sum.

Table

Chart

Number of employees grouped by department

Same configuration as in the above example, except the aggregate function of the salary column is set to count instead of sum.

Table

Chart

Salary pivoted by level, grouped by department

In the previous examples we always had only one data serie (salary or number of employees). It might also be interesting to define the data series based on available data.

In this example we want to see the summarized salaries grouped by department. Furthermore we want to display the data separated by the employees level.

To achieve this we have to group the employees by department and pivot the table by level. This results in the following table:

This table can be visualized in a chart as follows:

Chart configuration:

The list of pivot colors is automatically generated using existing data inside the table.

If a pivot column is set, it is only possible to add one value column.

Salary value column configuration: