GROUPBY

Syntax

GROUPBY (Association)

Description

Use the GROUPBY function in a condition to group detail members by summary members (for example, employees by department). The argument must be an association data cube; otherwise, the function returns an error.

Example

Suppose that an analytic model contains an association data cube called DEPARTMENTS, which associates each employee with a particular department. The following formula for the EMPLOYEES_IN_DEPT cube uses DCOUNT and GROUPBY to calculate the number of employees in each department:

DCOUNT(EMPLOYEES, GROUPBY(DEPARTMENTS))

The following formula for the AVG_SALARY_BY_DEPT data cube uses DAVG and GROUPBY to calculate the average salary for each department:

DAVG(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS))

You can combine the GROUPBY function with other conditions. For example, the following formula for the OFFICER_SALARIES_BY_DEPT cube uses the DSUM function to calculate the total officer salaries in each department. By combining IS_OFFICER with the GROUPBY function, the formula ensures that only officers are included in the sum:

DSUM(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS) .AND. IS_OFFICER)

Note that DSUM(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS)) is equivalent to GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, DEPARTMENTS). Using DSUM with GROUPBY is more flexible, because you can include other conditions, as shown in the formula above. On the other hand, the GROUPSUM function calculates significantly faster. For this reason, if you want to sum by group and you do not need to include other conditions, use the GROUPSUM function.