PMS Information Systems
Welcome to PMS Information Systems - IBM i (AS/400) Forum !!!

Get Answers for all your queries on IBM i (AS/400).

SQL: GROUPING SETS Statement

View previous topic View next topic Go down

SQL: GROUPING SETS Statement

Post  maran on Fri May 27, 2016 12:20 am

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

maran
Admin

Posts : 442
Join date : 2009-07-24

View user profile http://pmsinformationsystem.forumotion.net

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum