Advanced Grouping Sets

AnzoGraph DB supports creating advanced reports using grouping set extensions in the GROUP BY clause. Advanced grouping expressions enable users to conduct multidimensional analysis using a single statement in a single query to calculate different combinations of aggregations. This topic describes the AnzoGraph DB grouping set extensions:

• CUBE: Use CUBE expressions to generate subtotals for all combinations of different dimensions. For example, use CUBE to report on revenue by various dimensions such as region, time, and department.
• ROLLUP: Use ROLLUP expressions to generate subtotals for hierarchical levels of the same dimension, such as time or geography. For example, use ROLLUP to report on revenue by year, month, and day or by country, state, and city.
• GROUPING SETS: Use GROUPING SETS expressions to group the GROUP BY list into subsets.
• GROUPING: If a GROUP BY clause includes a CUBE, ROLLUP, or GROUPING SETS expression, the results of the expression might include unbound (NULL) values. Unbound values can result from either the WHERE clause operations (which are input to the GROUP BY clause) or the UNION of the individual grouping results in the GROUP BY clause. Using a GROUPING expression in conjunction with CUBE, ROLLUP, and GROUPING SETS enables users to determine the reason for an unbound result. GROUPING denotes the cause of an unbound result by returning 1 if the unbound value is the result of the grouping operation in the GROUP BY clause or 0 if the unbound value originated as input to the GROUP BY clause.