The GROUPING Function
You can use the ISNULL( ) function to replace NULL with the more meaningful <all> if the data contains no other NULLs. Suppose the underlying table included a row with NULL for some of the columns (assuming the table definition allowed nulls), and you inserted the following row:

INSERT product_sales values
	NULL,NULL,'US',10)

The last row in the cube above (having 343 as total sales for all U.S. products sold) would be indistinguishable from this data row. To differentiate the two, SQL Server provides the GROUPING( ) function. This function returns 1 (TRUE) if the element is an <all> value and 0 (FALSE) if the element is an actual data value or an explicit null. You can run the previous query again, after inserting null values in the row, to generate the value for the GROUPING( ) function:

SELECT 'Units'=SUM(units_sold),
	product, 'ALL Prods' =GROUPING(product),
	country, 'ALL Countries' =GROUPING(country),
	language, 'ALL Languages'
	=GROUPING(language)
	FROM product_sales2
	GROUP BY product, country, language
WITH CUBE

Table 5 shows the results. Note that the GROUPING() function takes a column name as an argument. A 0 returned for GROUPING(column_name) means the value returned for that column is a real value that occurs in the data. A 1 returned for GROUPING(column_name) means that the value returned for that column is not real; it was a special null returned because of the use of the CUBE (or ROLLUP) option. The first and last rows have the same values for product, country, and language, but different values in the GROUPING column because the first row corresponds to the raw data where product and country are null, and the nulls in the last row refer to all products and all countries.

In addition, you can use the CASE expression in SQL Server to differentiate between the GROUPING NULL, meaning all values, and the NULL meaning no value:

SELECTUnits = SUM(units_sold),
	Product = CASE    WHEN (GROUPING(product)=1) THEN 'ALL'
	    ELSE ISNULL(product, 'N/A')
    	END,
	Country = CASE    WHEN (GROUPING(country)=1) THEN 'ALL'
    	ELSE ISNULL(country, 'N/A')
    	END,
	Language = CASE    WHEN (GROUPING(language)=1) THEN 'ALL'
    	ELSE ISNULL(language, 'N/A')
    	END
	FROM product_sales2 
	GROUP BY product, country, language 
WITH CUBE
Prev. page     1 [2] 3     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

And exactly what database are these code examples running against?

Tom Verney- August 01, 2000

 
 

ADS BY GOOGLE