The GROUPING SETS clause is an extension to the GROUP BY clause of a SELECT statement. The GROUPING SETS clause allows you to group your results multiple ways, without having to use multiple SELECT statements to do so. This means you can reduce response time and improve performance.
For example, the following two queries statements are semantically equivalent. However, the second query defines the grouping criteria more efficiently using a GROUP BY GROUPING SETS clause.
Multiple groupings using multiple SELECT statements:
SELECT NULL, NULL, NULL, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) UNION ALL SELECT City, State, NULL, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY City, State UNION ALL SELECT NULL, NULL, CompanyName, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY CompanyName;
Multiple groupings using GROUPING SETS:
SELECT City, State, CompanyName, COUNT( * ) AS Cnt FROM Customers WHERE State IN ( 'MB' , 'KS' ) GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) );
Both methods produce the same results, shown below:
|4||(NULL)||(NULL)||Toto's Active Wear||1|
|5||(NULL)||(NULL)||North Land Trading||1|
|8||(NULL)||(NULL)||Overland Army Navy||1|
|9||(NULL)||(NULL)||Out of Town Sports||1|
Rows 2-9 are the rows generated by grouping over CompanyName, rows 10-12 are rows generated by grouping over the combination of City and State, and row 1 is the grand total represented by the empty grouping set, specified using a pair of matched parentheses (). The empty grouping set represents a single partition of all the rows in the input to the GROUP BY.
Notice how NULL values are used as placeholders for any expression that is not used in a grouping set, because the result sets must be combinable. For example, rows 2-9 result from the second grouping set in the query (CompanyName). Since that grouping set did not include City or State as expressions, for rows 2-9 the values for City and State contain the placeholder NULL, while the values in CompanyName contain the distinct values found in CompanyName.
Because NULLs are used as placeholders, it is easy to confuse placeholder NULLs with actual NULLs found in the data. To help distinguish placeholder NULLs from NULL data, use the GROUPING function.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|