SQL: GROUPING SETS Statement
Page 1 of 1
SQL: GROUPING SETS Statement
GROUPING SETS Statement
The GROUPING SETS statement enable one to get multiple GROUP BY result sets using a
single statement. It is important to understand the difference between nested (i.e. in secondary
parenthesis), and non-nested GROUPING SETS sub-phrases:
• A nested list of columns works as a simple GROUP BY.
• A non-nested list of columns works as separate simple GROUP BY statements, which are
then combined in an implied UNION ALL.
GROUP BY GROUPING SETS ((A,B,C)) is equivalent to GROUP BY A, B, C
GROUP BY GROUPING SETS (A,B,C) is equivalent to GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
GROUP BY C
GROUP BY GROUPING SETS (A,(B,C)) is equivalent to GROUP BY A
UNION ALL
GROUP BY B
,BY C
Multiple GROUPING SETS in the same GROUP BY are combined together as if they were
simple fields in a GROUP BY list:
GROUP BY GROUPING SETS (A) is equivalent to GROUP BY A
,GROUPING SETS (B) ,B
,GROUPING SETS (C) ,C
GROUP BY GROUPING SETS (A) is equivalent to GROUP BY A
,GROUPING SETS ((B,C)) ,B
,C
GROUP BY GROUPING SETS (A) is equivalent to GROUP BY A
,GROUPING SETS (B,C) ,B
UNION ALL
GROUP BY A
,C
We can mix simple expressions and GROUPING SETS in the same GROUP BY:
GROUP BY A, GROUPING SETS ((B,C)) is equivalent to GROUP BY A
,B
,C
The GROUPING SETS statement enable one to get multiple GROUP BY result sets using a
single statement. It is important to understand the difference between nested (i.e. in secondary
parenthesis), and non-nested GROUPING SETS sub-phrases:
• A nested list of columns works as a simple GROUP BY.
• A non-nested list of columns works as separate simple GROUP BY statements, which are
then combined in an implied UNION ALL.
GROUP BY GROUPING SETS ((A,B,C)) is equivalent to GROUP BY A, B, C
GROUP BY GROUPING SETS (A,B,C) is equivalent to GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
GROUP BY C
GROUP BY GROUPING SETS (A,(B,C)) is equivalent to GROUP BY A
UNION ALL
GROUP BY B
,BY C
Multiple GROUPING SETS in the same GROUP BY are combined together as if they were
simple fields in a GROUP BY list:
GROUP BY GROUPING SETS (A) is equivalent to GROUP BY A
,GROUPING SETS (B) ,B
,GROUPING SETS (C) ,C
GROUP BY GROUPING SETS (A) is equivalent to GROUP BY A
,GROUPING SETS ((B,C)) ,B
,C
GROUP BY GROUPING SETS (A) is equivalent to GROUP BY A
,GROUPING SETS (B,C) ,B
UNION ALL
GROUP BY A
,C
We can mix simple expressions and GROUPING SETS in the same GROUP BY:
GROUP BY A, GROUPING SETS ((B,C)) is equivalent to GROUP BY A
,B
,C
Similar topics
» Can I pass parameters to an SQL statement in a source member before executing the Run SQL Statement (RUNSQLSTM) command?
» Seton some Indicators at single statement
» Seton some Indicators at single statement
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|