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

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

Join the forum, it's quick and easy

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

Get Answers for all your queries on IBM i (AS/400).
PMS Information Systems
Would you like to react to this message? Create an account in a few clicks or log in to continue.

SQL: GROUPING SETS Statement

Go down

SQL: GROUPING SETS Statement Empty SQL: GROUPING SETS Statement

Post  maran 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
maran
Admin

Posts : 442
Join date : 2009-07-24

https://pmsinformationsystem.forumotion.net

Back to top Go down

Back to top

- Similar topics

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