Common Oracle DBA statements 10 (group)

######################### Enhanced Group by clause ######### ############### select [column,] group_function (column )... from table [Where condition] [group by [rollup] group_by_expression] [having having_expression]; [order by column]; ------- rollup operation word, aggregate the fields in the group by clause from right to left. Example:/* The result looks like subtotal for col1 */select col1, col2, sum (col3) from table group by rollup (col1, col2);/* Composite rollup expression */select col1, col2, sum (col3) from Table group by rollup (col1, col2 )); select [column,] group_function (column )... from table [Where condition] [group by [cube] group_by_expression] [having having_expression]; [order by column]; ------- cube operator word, except for the rollup function, then, the result set after rollup is aggregated from right to left. Example:/* The result looks like a subtotal of col1, then a subtotal of col2, and finally a total of */select col1, col2, sum (col3) from Table group by cube (col1, col2);/* Composite rollup expression */select col1, col2, sum (col3) from table group by cube (col1, col2);/* Mixed rollup, cube expression */select col1, col2, col3, sum (col4) from Table group by col1, rollup (col2), cube (col3);/* grouping (expr) function, which can be used to check which segments are used for the SELECT statement. The value is 0 or 1 */select [column,] group_function (column )..., grouping (expr) from table [Where condition] [group by [rollup] group_by_expression] [having having_expression]; [order by column]; example: Select col1, col2, sum (col3), grouping (col1), grouping (col2) from Table group by cube (col1, col2);/* grouping sets operation, sums col1 for the group by result set first, then sum col2 and combine the result sets. */select col1, col2, sum (col3) from Table group by grouping sets (col1), (col2 ));