| country| city | total_sales | GPID (integer) | Whereas GROUPING(country) + GROUPING(city) returns a binary string. However, GROUPING_ID(country, city) returns an integer value In this example the GROUPING_ID(country, city) function is equivalent to GROUPING(country) Select country, city, sum(sales) as total_sales The following example shows GROUPING_ID applied to the column “country, city” and also matches the "column_expression" in the GROUP BY list. This function can be used only in the SELECT list, HAVING, or ORDER BY clauses when GROUP BY is specified. GROUPING_ID() with no arguments is equivalent to GROUPING_ID() with all of the grouping columns specified in the group by clause. GROUPING_ID function performs the binary to decimal conversion and returns a BIGINT. This function can be used with multiple columns however they must match with the GROUP BY column list. The integer value can be interpreted as aīit vector where bits in the binary representation of the integer correspond to each Unique for each combination of grouping values. GROUPING_ID function computes the level of grouping and returns an integer value which is SInce this is an actual NULL value in the raw data and does not indicate an aggregate theĬorresponding GROUPING alias GP_city column is indicated by 0. NULL value in the 6th row for the column city shows the sales for an UNKNOWN city in Japan. Since this is an aggregated value the result setįor the GROUPING (city) is indicated by 1 in the GROUPING alias GP_city column for the corresponding row. This is the summary row addedīy the ROLLUP operation on the column "city". Total sales for both cities (San jose and Fremont) in the US. In the above example the value “NULL” in the third row for the column "city" represents the | country| city | total_sales | GP_country | GP_city | The following example shows GROUPING function applied to the columns "country" and So these functions can be used to distinguish between a NULL value in the raw data vs the TOTAL across all values that are returned by ROLLUP, CUBE or GROUPING SETS for a specific column. Grouping returns 1 if the data is aggregated across the specified column or 0 for not aggregated in the result set. This function can be used only on a single column. GROUPING (column) function indicates whether the column in a GROUP BY list is aggregated or not. These aggregate functions are used in conjunction with the GROUP BY ROLLUP, CUBE, and
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |