WL#1979: GROUPING() function
Affects: WorkLog-3.4 — Status: Un-Assigned — Priority: Medium
The function GRIUPING() is used with ROLLUP/CUBE GROUPING(column) returns 1 if the column's value in the row was generated by rollup/cube: The SQL:2003 standard specifies: The grouping operation is of the form GROUPING(<column reference>). The result of such an invocation is 1 (one) in the case of a row whose values are the results of aggregation over that <column reference> during the execution of a grouped query containing CUBE, ROLLUP, or GROUPING SET, and 0 (zero) otherwise. The function exists in MSSQL and Oracle: http://www.orafusion.com/art_agg.htm http://www.winnetmag.com/SQLServer/Articles/ArticleID/5104/pg/2/2.html OLAP feature. Feature requests: BUG#3156 GROUPING() Feature request
Peter suggests: * The data type of GROUPING is INTEGER. The standard only requires that it be exact numeric with scale 0. DB2 says "small integer". SQL Server says "tinyint". But it's easier to remember if most functions return integers. * GROUPING(x) is an error if x isn't a column in GROUP BY. I suppose the error would be similar to ERROR 1055 (42000): x isn't in GROUP BY but I doubt that it should depend on whether sql_mode=ONLY_FULL_GROUP_BY. * GROUPING is not a new reserved word. But "create function grouping ..." will cause the note-level warning 1585 "This function 'grouping' has the same name as a native function". * GROUPING may only be in a select list. Therefore it may not be in a HAVING clause or an ORDER BY clause. And it may not be in a subquery which is inside the select list. But it may be in a scalar function which is inside the select list. * The HLD quotation (which says return 1 "in the case of a row whose values are the results of aggregation") is too vague, because GROUPING can produce different values (0 or 1) for different columns in the same row. [...] Perhaps: "If the column value is not null, return 0. If the column value is null because the original is null, return 0. If the column value is null because ROLLUP produced it, return 1."
Copyright (c) 2000, 2016, Oracle Corporation and/or its affiliates. All rights reserved.